Insert single xml element into sql like string manipulation

Question

Hi I have a XML file like this

<pictures>
<mainimg>
<link>http://s.freshnewtracks.com/wp-content/uploads/2012/07/dr_dre_20011.jpeg</link>
</mainimg>
<img>
<title>back</title>
<link>http://s.freshnewtracks.com/wp-content/uploads/2012/07/dr_dre_20011.jpeg</link>
</img>
<web></web>
<twitter></twitter>
<facebook></facebook>
<myspace></myspace>
<about></about>
</pictures>

I need to INSERT this XML into a SQL Datatable without using XML file on disk. I have seen other examples where it is done by using a xml file on disk. I want to do this with typed/string XML without saving to disk/BULK Link For eg something like

INSERT INTO Table (XMLColumn)
VALUES (XML LIKE STRING)

Is this possible? Can I have some controls over the elements of XML file for each row?

You should be able to use parameterised SQL using the SqlDbType.Xml parameter type. Are you looking for something like stackoverflow.com/questions/3557882/… and stackoverflow.com/questions/3600091/… — dash
– dash, Commented May 21, 2013 at 10:39

Saurabh · Accepted Answer · 2013-05-21 10:41:30Z

1

Pass this XML as string to stored procedure and get this xml string by @XML XML = null in the stored procedure

In the SP:

insert into Table(column_name) 
                                      SELECT 
         @Applicationo_new
            ,column_name  = t.p.value('column_name', 'varchar(20)') 
    FROM @XML.nodes('pictures') t(p);

answered May 21, 2013 at 10:41

Saurabh

5,7272 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert single xml element into sql like string manipulation

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related