CODEWITHSUNDEEP
sqlsql-serverxml
Sergеу Isupov
Sergеу Isupov

Reputation: 504

How can I specify namespace not in root tag?

How can I get an XML with the following structure:

<Test xmlns="http://default.namespace.com">
    <Child xmlns:ovrd="http://overridden.namespace.com">
        <ovrd:GrandChild>123</ovrd:GrandChild>
    </Child>
</Test>

As I understand WITH NAMESPACES allows add a namespace only into the root tag.

create table Children (
    Id int, 
    GrandChild varchar(50)
)
GO

insert into Children values (1, '123')

GO

WITH XMLNAMESPACES (
        default 'http://default.namespace.com'      
)  
select GrandChild as 'Child/GrandChild'
from Children
where id = 1
for xml PATH('Test'), ELEMENTS XSINIL

I need to specify a namespace somehow in Child tag

Upvotes: 0

Views: 150

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22177

XQuery to the rescue. It allows to specify multiple namespaces on a different level.

SQL

-- DDL and sample data population, start
DECLARE @mockChildren TABLE  (
    Id INT, 
    GrandChild VARCHAR(50)
)

INSERT INTO @mockChildren VALUES (1, '123');
-- DDL and sample data population, end


;WITH XMLNAMESPACES (DEFAULT 'http://default.namespace.com')  
SELECT (
    SELECT GrandChild AS 'GrandChild'
    FROM @mockChildren
    WHERE id = 1
    FOR XML PATH('row'), TYPE, ROOT('root')
).query('<Test xmlns="http://default.namespace.com">
{ 
    for $r in /root/row
    return 
    <Child xmlns:ovrd="http://overridden.namespace.com">
        <ovrd:GrandChild>
            {data($r/GrandChild)}
        </ovrd:GrandChild>
    </Child>
}
</Test>');

XML Output

<Test xmlns="http://default.namespace.com">
  <Child xmlns:ovrd="http://overridden.namespace.com">
    <ovrd:GrandChild>123</ovrd:GrandChild>
  </Child>
</Test>

Upvotes: 2

Related Questions