Add JSON Valuetypes to XML Nodes using SQL

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

<Main xmlns:json="http://www.samplenamespace.com/json">
<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.

xmlns:json="http://www.samplenamespace.com/json"

desired outcome:

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


rextester link: http://rextester.com/FAV86925

any help?!

1 Answer
1

I don't know if I've got this correctly, but this query would create your XML in one go. If there is no 1:n relation for <details> you don't need the sub-select.

1:n

<details>

btw: You should not store the age as int but store the DOB and compute the age on demand.

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)

;WITH XMLNAMESPACES ('http://www.samplenamespace.com/json' as json)
select 'Number' AS [ID/@json:ValueType]
,cd1.cid AS ID
,cd1.[name] AS [details/name]
,cd1.age AS [details/age]
from #Cdetails cd1
For XML Path('Main');


The result

<Main xmlns:json="http://www.samplenamespace.com/json">
<ID json:ValueType="Number">1001</ID>
<details>
<name>John</name>
<age>12</age>
</details>
</Main>
<Main xmlns:json="http://www.samplenamespace.com/json">
<ID json:ValueType="Number">1002</ID>
<details>
<name>Rick</name>
<age>19</age>
</details>
</Main>
... more of them


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.