SQL server insert data from table into an XML variable

Question

How can I insert a whole bunch of rows into an XML variable without using a cursor? I know 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, isn't legal syntax.

Edit: Obviously my question wasn't clear. What I want is to be able to do 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>

Answer 1

LAST UPDATE:

OK, now that the question is much clearer, he's the solution - hopefully!!

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

This 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 the UPDATE:

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

Is THAT what you're looking for?? :-)

---

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

You need to look at the .nodes() function in SQL XQuery - this will break up an XML variable into a list of XML nodes, based on an XPath expression (that references some point in your XML where you are likely to have an enumeration of nodes of the same structure), and it gives them a "virtual" table and column name.

Based on that "Table.Column" element, you can select single values from that XML node - either attributes or sub-elements - and you get these back as "atomic" values, e.g. as INT, VARCHAR(x), whatever 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 - that'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 the UPDATE statement with the .modify() call will actually insert that content into an existing XML field in your database. This is the only way to get XML contents into an existing XML column - there's no way of directly referencing another XML column inside a XML fragment being inserted....

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

And of course, you can easily store that into a XML variable.

Marc

Answer 2

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>

Answer 3

It's a bit late but isn't this simpler?

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