Concatenate XML variables in SQL Server

Question

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 :)

Saurabh R S · Accepted Answer · 2012-10-08 18:02:24Z

1

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 !

answered Oct 8, 2012 at 18:02

Saurabh R S

3,1871 gold badge38 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Taryn · Accepted Answer · 2012-10-08 18:10:58Z

1

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)

answered Oct 8, 2012 at 18:10

Taryn

249k57 gold badges374 silver badges409 bronze badges

Comments

nenea · Accepted Answer · 2021-07-20 00:43:15Z

0

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

edited Jul 20, 2021 at 0:43

answered Jul 2, 2021 at 12:08

nenea

1478 bronze badges

Collectives™ on Stack Overflow

Concatenate XML variables in SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related