Selecting columns as XML with namespace

Question

I need to select some columns from a table as XML with namespaces included in them along with other columns as is. For example, I have a following table layout:

ID  C1  X1C1  X1C2  X2C3
1   A   1     2     3

What the query should return is:

ID  C1  XmlData
1   A   <xmldata1>
2   A   <xmldata2>

Where <xmldata1> would be:

<Root xmlns:xsd="w3.org/2001/XMLSchema" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:mst="microsoft.com/wsdl/types/">
  <Child attrib="C1">
    <ChildValue xsi:type="xsd:integer">1</ChildNode>
  </Child>
  <Child attrib="C2">
    <ChildNode xsi:type="xsd:integer">2</ChildNode>
  </Child>
</Root>

and <xmldata2> would be:

<Rootxmlns:xsd="w3.org/2001/XMLSchema" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:mst="microsoft.com/wsdl/types/">
  <Child attrib="C3">
    <ChildNode xsi:type="xsd:integer">3</ChildNode>
  </Child>
</Root>

I have a good reference how to build the xml from this SO question but I'm not able to put in the namespaces. If this is possible how to do it?

Edit: I've used following query attempting to get the required result: select 1 ID, 'A' C1, 1 X1C1, 2 X1C2, 3 X2C3 into #t

;with xmlnamespaces('w3.org/2001/XMLSchema' as xsd, 'w3.org/2001/XMLSchema-instance' as xsi, 'microsoft.com/wsdl/types/' as mst)
select ID, C1, (select (SELECT 'C1' "@attrib", 'xsd:integer' "ChildValue/@xsi:type",t.X1C1 as 'ChildValue' FOR XML PATH('Child'), type),(SELECT 'C2' "@name", 'xsd:integer' "ChildValue/@xsi:type", t.X1C2 as 'ChildValue' FOR XML PATH('Child'), type) FOR XML PATH('Root'), type) as property_data 
FROM #t t

drop table #t

Here is the output of its xml part:

<Root xmlns:mst="microsoft.com/wsdl/types/" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:xsd="w3.org/2001/XMLSchema">
<Child xmlns:mst="microsoft.com/wsdl/types/" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:xsd="w3.org/2001/XMLSchema" attrib="C1">
    <ChildValue xsi:type="xsd:integer">1</ChildValue>
  </Child>
  <Child xmlns:mst="microsoft.com/wsdl/types/" xmlns:xsi="w3.org/2001/XMLSchema-instance" xmlns:xsd="w3.org/2001/XMLSchema" name="C2">
    <ChildValue xsi:type="xsd:integer">2</ChildValue>
  </Child>
</Root>

I can't get rid of the namespaces in the Child node.

Have a look at: [How to remove xmlns from child elements with FOR XML](http://dba.stackexchange.com/a/23697/2103) — Mikael Eriksson, May 07 '14 at 13:33

score 5 · Accepted Answer · edited May 23 '17 at 11:43

I used this solution: TSQL for xml add schema attribute to root node

Basically, I did not put the namespace in the beginning but after generating the required xml structure I casted the xml to nvarchar(max) and replaced the root node with the desired namespace.

I also needed to use namespace prefix in the attribute. For that I used a pseudo attribute name which I replaced with a proper xml namespace prefix.

Both operations were done using tsql REPLACE function. Hacky but couldn't find other proper ways to do it.

Jayvee · Answer 2 · 2014-04-25T18:52:17.327

1

you need to include WITH xmlnamespaces , example:

;with xmlnamespaces('w3.org/2001/XMLSchema' as xsd, 'w3.org/2001/XMLSchema-instance' as xsi, 'microsoft.com/wsdl/types/' as mst)
select ID, C1,
(select 
(SELECT 'C1' "@name",t.C1 as 'value'FOR XML PATH('Property'), type),
(SELECT 'C2' "@name",t.C2 as 'value'FOR XML PATH('property'), type)
FOR XML PATH('data'), type) as property_data 
FROM TableName t

edited Apr 25 '14 at 18:52

answered Apr 25 '14 at 15:47

Jayvee

10,670
3
29
40

I have this query: `select ID, C1,(select (SELECT 'C1' "@name",t.C1 as 'value'FOR XML PATH('Property'), type),(SELECT 'C2' "@name",t.C2 as 'value'FOR XML PATH('property'), type)FOR XML PATH('data')) as property_data FROM TableName t` and `with` is not supported there. Got any alternatives? – rageit Apr 25 '14 at 17:58
I changed the sql using your example. Note that I added the ;with xmlnamespaces line and also added 'type' to the outer node. It seems to work fine. Please try it and let me know. – Jayvee Apr 25 '14 at 18:54
With this query Property node also gets namespaces. I need namespaces only at the root node. – rageit Apr 25 '14 at 21:22

score 1 · Answer 3 · answered May 07 '14 at 13:10

1

Have you tried like? select XML_COL_NAME.value('(/rootNode//childNode/node())[1]', 'nvarchar(64)') from tableName

answered May 07 '14 at 13:10

Param Pavar

11
2

I was not sure if using namespace was a must for you. Hope this help. – Param Pavar May 07 '14 at 13:11

Selecting columns as XML with namespace

3 Answers3