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 http://stackoverflow.com/questions/3557882/stored-procedure-pass-xml-as-an-argument-and-insert-key-value-pairs/3558102#3558102 and http://stackoverflow.com/questions/3600091/how-to-pass-xml-from-c-sharp-to-a-stored-procedure-in-sql-server-2008?lq=1 — dash, May 21 '13 at 10:39

score 1 · Accepted Answer · answered May 21 '13 at 10:41

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);

Insert single xml element into sql like string manipulation

1 Answers1