All geek questions in one place

Want to save XML in a SQL Server 2014 table and apply the selection on the same

Availability of data in XML for XML and I want to save this XML in a SQL Server table. I created an xml data type column and tried to save my XML data in this newly created column, but I get an INSERT error message:

Invalid data type "xml" used in VALUE method.

This is the INSERT :

 INSERT INTO UserDeatailtbl(RoleXml) SELECT Y.ID.value('./RoleXml[1]', 'XML') FROM @UserDetailsXML.nodes('UserDetails/UserDetailsRow') AS Y(ID)
+5
source share
1 answer

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

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

Perhaps this will work, as the .query() operator returns the definition of the XML fragment by definition.

If this does not work, we will need to learn more about your XML structure - can you show us an example of your XML stored in the @UserDetailsXML variable? What part of this do you want to insert into UserDetailTbl.RoleXml ?

+3
source

Source: https://habr.com/ru/post/1276175/

More articles:


All Articles