How to add namespace to SQL Server generated XML output

Question

I have the below XML output generated using a SQL Server query (added in the rextester link):

<Main>
    <ID>1001</ID>
    <details>
        <name>John</name>
        <age>12</age>
    </details>
</Main>

I want to know how to add a namespace xmlns:json="http://www.samplenamespace.com/json" to the Main node.

Desired output:

<Main xmlns:json="http://www.samplenamespace.com/json">
    <ID>1001</ID>
    <details>
        <name>John</name>
        <age>12</age>
    </details>
</Main>

Rextester link: http://rextester.com/OQZH6668

Any help!?

DavidG · Accepted Answer · 2018-06-28 18:56:12Z

3

You need to use the WITH XMLNAMESPACES clause, for example:

---Fake tables in Stored procedure1
create table #Cdetails(cid int, name varchar(5), age int)
insert into #Cdetails
values(1001,'John',12),
(1002,'Rick',19),
(1003,'Diane',25),
(1004,'Kippy',26)

--Output of Stored procedure 

create table #final(xml_data xml)
insert into #final
select
XML_data =  
    (select ID = cd1.cid,
    details =
        (
        select cd.name,
        cd.age
        from #Cdetails cd
        where cd.cid = cd1.cid
        For XML Path(''), Type)
    from #Cdetails cd1
    For XML Path('Main'));


WITH XMLNAMESPACES ('http://www.samplenamespace.com/json' as json)  
select * from #final
For XML Path('Main')

drop table #Cdetails,#final

Note the extra ; that is required when using WITH statements.

Rextester link: http://rextester.com/EBLL48414

answered Jun 28, 2018 at 18:56

DavidG

120k13 gold badges232 silver badges239 bronze badges

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

2 Comments

Rick Over a year ago

Thanks. How can I add a property like 'json:ValueType="Number" ' to a node? I want to add <age json:ValueType="Number">12</age>. Can you sample that one too?

Rick Over a year ago

Understand. I was doubtful if that question may be closed saying it's redundant

Collectives™ on Stack Overflow

How to add namespace to SQL Server generated XML output

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related