In SQL Server can I insert multiple nodes into XML

Question

I want to generate some XML in a stored procedure based on data in a table.

The following insert allows me to add many nodes but they have to be hard-coded or use variables (sql:variable):

SET @MyXml.modify('
      insert
         <myNode>
            {sql:variable("@MyVariable")}
         </myNode>
      into (/root[1]) ') 

So I could loop through each record in my table, put the values I need into variables and execute the above statement.

But is there a way I can do this by just combining with a select statement and avoiding the loop?

Edit I have used SELECT FOR XML to do similar stuff before but I always find it hard to read when working with a hierarchy of data from multiple tables. I was hoping there would be something using the modify where the XML generated is more explicit and more controllable.

Answer 1

Use sql:column instead of sql:variable. You can find detailed info here: http://msdn.microsoft.com/en-us/library/ms191214.aspx

Answer 2

Have you tried nesting FOR XML PATH scalar valued functions? With the nesting technique, you can brake your SQL into very managable/readable elemental pieces

Disclaimer: the following, while adapted from a working example, has not itself been literally tested

Some reference links for the general audience

• http://msdn2.microsoft.com/en-us/library/ms178107(SQL.90).aspx
• http://msdn2.microsoft.com/en-us/library/ms189885(SQL.90).aspx

The simplest, lowest level nested node example

Consider the following invocation

DECLARE  @NestedInput_SpecificDogNameId int
SET @NestedInput_SpecificDogNameId = 99
SELECT [dbo].[udfGetLowestLevelNestedNode_SpecificDogName] 
(@NestedInput_SpecificDogNameId)

Let's say had udfGetLowestLevelNestedNode_SpecificDogName had been written without the FOR XML PATH clause, and for @NestedInput_SpecificDogName = 99 it returns the single rowset record:

@SpecificDogNameId  DogName
99                  Astro

But with the FOR XML PATH clause,

CREATE FUNCTION dbo.udfGetLowestLevelNestedNode_SpecificDogName
(
@NestedInput_SpecificDogNameId
)
    RETURNS XML
    AS
    BEGIN

        -- Declare the return variable here
        DECLARE @ResultVar XML

        -- Add the T-SQL statements to compute the return value here
        SET @ResultVar =
            (
            SELECT 
                  @SpecificDogNameId as "@SpecificDogNameId",
                  t.DogName 
            FROM tblDogs t
            FOR XML PATH('Dog')
            )

        -- Return the result of the function
        RETURN @ResultVar

END

the user-defined function produces the following XML (the @ signs causes the SpecificDogNameId field to be returned as an attribute)

<Dog SpecificDogNameId=99>Astro</Dog>

Nesting User-defined Functions of XML Type

User-defined functions such as the above udfGetLowestLevelNestedNode_SpecificDogName can be nested to provide a powerful method to produce complex XML.

For example, the function

CREATE FUNCTION [dbo].[udfGetDogCollectionNode]()
    RETURNS XML
    AS
    BEGIN

        -- Declare the return variable here
        DECLARE @ResultVar XML

        -- Add the T-SQL statements to compute the return value here
        SET @ResultVar =
            (
                SELECT  
                [dbo].[udfGetLowestLevelNestedNode_SpecificDogName]
                        (t.SpecificDogNameId)
                FROM tblDogs t

                FOR XML PATH('DogCollection') ELEMENTS
            )
        -- Return the result of the function
        RETURN @ResultVar

END

when invoked as

SELECT [dbo].[udfGetDogCollectionNode]()

might produce the complex XML node (given the appropriate underlying data)

<DogCollection>
    <Dog SpecificDogNameId="88">Dino</Dog>
    <Dog SpecificDogNameId="99">Astro</Dog>
</DogCollection>

From here, you could keep working upwards in the nested tree to build as complex an XML structure as you please

CREATE FUNCTION [dbo].[udfGetAnimalCollectionNode]()
RETURNS XML
AS
BEGIN

DECLARE @ResultVar XML

SET @ResultVar =
(
SELECT 
dbo.udfGetDogCollectionNode(),
dbo.udfGetCatCollectionNode()
FOR XML PATH('AnimalCollection'), ELEMENTS XSINIL
)

RETURN @ResultVar

END

when invoked as

SELECT [dbo].[udfGetAnimalCollectionNode]()

the udf might produce the more complex XML node (given the appropriate underlying data)

<AnimalCollection>
  <DogCollection>
    <Dog SpecificDogNameId="88">Dino</Dog>
    <Dog SpecificDogNameId="99">Astro</Dog>
  </DogCollection>
  <CatCollection>
    <Cat SpecificCatNameId="11">Sylvester</Cat>
    <Cat SpecificCatNameId="22">Tom</Cat>
    <Cat SpecificCatNameId="33">Felix</Cat>
  </CatCollection>
</AnimalCollection>

Answer 3

Can you tell a bit more about what exactly you are planning to do. Is it simply generating XML data based on a content of the table or adding some data from the table to an existing xml structure?

There are great series of articles on the subject on XML in SQLServer written by Jacob Sebastian, it starts with the basics of generating XML from the data in the table