Geek Answers Handbook

SQL server inserts data from table into XML variable

How can I insert a whole string of strings into an XML variable without using a cursor? I know what I can do

SET @errors.modify('insert <error>{ sql:variable("@text") }</error> as last into /errors[1]')

to insert the value of a variable, but I want to basically do

 SET @errors.modify(SELECT 'insert <error>{ sql:column("text") }</error>' FROM table)

which, of course, is not legal syntax.

Edit: Obviously, my question is not clear. I want this possible to be done like this:

 CREATE TABLE my_table(text nvarchar(50)) INSERT INTO my_table VALUES('Message 2') INSERT INTO my_table VALUES('Message 3') DECLARE @errors xml SET @errors = '<errors><error>Message 1</error></errors>' SET @errors.modify('INSERT EVERYTHING FROM my_table MAGIC STATEMENT')

And after running this code, @errors should contain

 <errors> <error>Message 1</error> <error>Message 2</error> <error>Message 3</error> </errors>
+4
source share
3 answers

Based on marc's answer, here is a solution that works for SQL Server 2005:

 CREATE TABLE #my_table(text nvarchar(50)) INSERT INTO #my_table VALUES('Message 2') INSERT INTO #my_table VALUES('Message 3') DECLARE @errors xml SET @errors = '<errors><error>Message 1</error></errors>' SELECT @errors = CAST(@errors AS nvarchar(max)) + '<new>' + (SELECT text AS 'error' FROM #my_table FOR XML PATH(''), ELEMENTS) + '</new>' SET @errors = CAST(@errors AS nvarchar(max)) + '<new>' + @newErrors + '</new>' SET @errors.modify('insert (/new/*) as last into (/errors)[1]') SET @errors.modify('delete (/new)') SELECT @errors DROP TABLE #my_table

Will return

 <errors> <error>Message 1</error> <error>Message 2</error> <error>Message 3</error> </errors>
0
source

LAST UPDATE:

OK, now that the question is much clearer, it is a solution - I hope!

 DECLARE @errors xml SET @errors = '<errors><error>Message 1</error></errors>' DECLARE @newErrors XML SELECT @newErrors = (SELECT text AS 'error' FROM dbo.my_table FOR XML PATH(''), ELEMENTS) SELECT @errors, @newErrors SET @errors.modify('insert sql:variable("@newErrors") as last into (/errors)[1]') SELECT @errors

It gives me

@errors at the beginning

 <errors><error>Message 1</error></errors>

@newError after the magic SELECT:

 <error>Message 2</error><error>Message 3</error>

@errors after UPDATE:

 <errors> <error>Message 1</error> <error>Message 2</error> <error>Message 3</error> </errors>

Is that what you are looking for? :-)

(old answers are not what the OP was looking for).

You need to look at the .nodes() function in SQL XQuery - this will cause the XML variable to decay into a list of XML nodes based on an XPath expression (which refers to some point in your XML, where you will probably list the nodes of the same structures) and gives them the "virtual" name of the table and column.

Based on this “Table. Column” element, you can select individual values ​​from this XML node — either attributes or sub-elements — and you will get them back as “atomic” values, for example. like INT, VARCHAR (x) is what you need. These values ​​can be inserted into the table:

 INSERT dbo.YourTable(col1, col2, col3, ..., colN) SELECT Error.Column.value('@attr1[1]', 'varchar(20)'), Error.Column.value('subitem[1]', 'int'), ..... Error.Column.value('subitemN[1]', 'DateTime') FROM @xmldata.nodes('/error') AS Error(Column)

UPDATE: ok, so you want to do the opposite - turn relational data into XML - it's even easier :-)

 DECLARE @NewXmlContent XML SELECT @NewXmlContent = (SELECT col1 as '@ID', col2 as 'SomeElement', ..... colN as 'LastElement' FROM dbo.YourTable WHERE .... FOR XML PATH('element'), ROOT('root') ) UPDATE YourOtherTable SET XmlField.modify('insert sql:variable("@NewXmlContent") as last into (/XPath)[1]') WHERE (some condition)

This will give you something like this in @NewXmlContent:

 <root> <element ID="(value of col1)"> <SomeElement>(value of col2)</SomeElement> ..... <LastElement>(value of colN)</LastElement> </element> </root>

and an UPDATE statement with a call to .modify() actually inserts this content into an existing XML field in your database. This is the only way to get the XML content into an existing XML column - there is no way to directly reference another XML column inside the inserted XML fragment ....

The new "FOR XML PATH" syntax is very powerful and flexible and allows you to do anything.

And of course, you can easily save this in an XML variable.

Mark

+1
source

Isn't that easier?

 set ErrorXML=(SELECT * from #MyTable FOR XML AUTO)
+1
source

More articles:


All Articles