CODEWITHSUNDEEP
sqlxmlsql-server-2008t-sql
RichardB
RichardB

Reputation: 606

Concatenate XML variables in SQL Server

I have a query in a stored procedure retrieving some data in XML format to be returned in a variable @xml_data, like this:

SELECT @xml_data = (
    SELECT * FROM (
        SELECT   1 AS Tag
                ,0 AS Parent
                .....
        FROM    MyTable
        WHERE   id = @id

        UNION ALL

        SELECT   2 AS Tag
                ,1 AS Parent
                ....
        FROM    MyTable2
        WHERE   id = @id

        UNION ALL

        SELECT   3 AS Tag
                ,2 AS Parent
                ....
        FROM    MyTable3
        WHERE   id = @id
    ) results
FOR XML EXPLICIT, TYPE)

This is working like the proverbial dream :)

However, I'd like to concatenate a header to this XML (e.g. <xml version="1.0" encoding="ISO-8859-1"/>) and can't figure out how to do it. I've tried converting to NVARCHAR, selecting the two variables in one statement but can't seem to get it right.

Can anyone help??

Thanks :)

Upvotes: 0

Views: 5957

Answers (3)

nenea
nenea

Reputation: 147

This is the easiest way, in my opinion:

declare @xml1 xml
declare @xml2 xml
declare @xml3 xml
select @xml1='<name>testname</name>'
select @xml2='<value>testvalue</value>'
select @xml3 = 
(
    select @xml1 AS xml1, @xml2 AS xml2
    for xml path('')
)
select @xml3

Upvotes: 0

Taryn
Taryn

Reputation: 247860

You can just declare the string at the beginning and concatenate them together:

declare @xml_data nvarchar(MAX)

set @xml_data = '<xml version="1.0" encoding="ISO-8859-1"/>'

SELECT @xml_data = @xml_data +  (
    SELECT * FROM (
        SELECT   1 AS Tag
                ,0 AS Parent
                .....
        FROM    MyTable
        WHERE   id = @id

        UNION ALL

        SELECT   2 AS Tag
                ,1 AS Parent
                ....
        FROM    MyTable2
        WHERE   id = @id

        UNION ALL

        SELECT   3 AS Tag
                ,2 AS Parent
                ....
        FROM    MyTable3
        WHERE   id = @id
    ) results
FOR XML EXPLICIT, TYPE)

Upvotes: 1

Saurabh R S
Saurabh R S

Reputation: 3177

Try doing like this: 

DECLARE @x xml 
DECLARE @strXML varchar(MAX)
SET @x=N'<Employee><Name>Luftwaffe</Name></Employee>'
set @strXML = '<xml version="1.0" encoding="ISO-8859-1"/>' + CONVERT(varchar(MAX),@x)
SELECT @strXML

Hope it helps !

Upvotes: 1

Related Questions