Want to store XML in SQL Server 2014 table and apply select on same

5
votes

Having data in the for of XML and I want to store that XML in a SQL Server table. I have created a column of datatype xml and tried to store my XML data in that newly created column, but I get an error on INSERT:

The data type 'xml' used in the VALUE method is invalid.

This is the Insert statement:

INSERT INTO UserDeatailtbl(RoleXml)
    SELECT Y.ID.value('./RoleXml[1]', 'XML') 
    FROM @UserDetailsXML.nodes('UserDetails/UserDetailsRow') AS Y(ID)
1
sql-serverxmlsql-server-2014
It would help tp provide a reduced example of your XML. Assumption: There is a bigger XML including many <UserDetailsRow> nodes and you try to create one row per each. Wild guess: You do not want to store the XML as XML but rather want to read the included values into your table's columns? Please state the import (your XML) and the expected result. – Shnugo
@Shnugo Its a nested Xml. Outer XMl having two nodes that i am storing in table but 3rd node is XML that i want to store as it is in table. – Swapnali Hadawale
Without knowing your XML it is guessing... Does your table include just one single column RoleXml? What does outer XML mean? What does storing in table mean? If @marc_s answer does not help you out, please provide more details... – Shnugo
@Shnugo the given solution is the exact what i was looking for. I am getting desired output by given solution. – Swapnali Hadawale
In this case you should accept the answer (click the check below the vote counter). You are - additionally - asked to vote on contributions. Votes and acceptance are different actions. – Shnugo

1 Answers

3
votes

Try to use the .query() operator instead of .value() - like this:

INSERT INTO UserDetailtbl(RoleXml)
    SELECT ID.query('.') 
    FROM @UserDetailsXML.nodes('UserDetails/UserDetailsRow/RoleXml') AS Y(ID)

Maybe that'll work, since the .query() operator is returning an XML fragment by definition.

If it doesn't work, we'll need to know more about your XML structure - can you show us an example of your XML stored in the @UserDetailsXML variable? Which part of that do you want to insert into UserDetailTbl.RoleXml?