I have several SVG-segments in a table stored as xml-document. Now I need to select all elements from that table, and merge them into one XML-document.
This is the T-SQL code I have:
declare @xml table (xmldocument xml)
insert @xml select '
<svg xmlns="http://www.w3.org/2000/svg" otherattrib="x">
<path id="789" data-objid="0000X2"></path>
</svg>'
insert @xml select '
<svg xmlns="http://www.w3.org/2000/svg" otherattrib="x">
<admin>
<g>
<path></path>
<path data-objid="0000X1"></path>
<path id="123" data-objid="0000X2"></path>
<path id="456" data-objid="0000X3"></path>
</g>
</admin>
<g>
<path></path>
<path data-objid="0000X1"></path>
<path id="789" data-objid="0000X2"></path>
<path id="abc" data-objid="0000X3"></path>
</g>
</svg>'
insert @xml select '
<svg xmlns="http://www.w3.org/2000/svg" otherattrib="x">
<path></path>
</svg>'
insert @xml select '
<svg xmlns="http://www.w3.org/2000/svg" otherattrib="x">
<path id="abc" data-objid="0000X3"></path>
</svg>'
--;WITH XMLNAMESPACES ('http://www.w3.org/2000/svg' AS svg)
;WITH XMLNAMESPACES (default 'http://www.w3.org/2000/svg')
--SELECT
--(
SELECT
--xmldocument
--,c.p.value('.', 'nvarchar(MAX)')
c.p.query('declare default element namespace "http://www.w3.org/2000/svg";.')
FROM @xml AS t
OUTER APPLY t.xmldocument.nodes('/svg//*') AS c(p)
FOR XML PATH(''), root('svg')
-- ) AS merged
But this produces
<svg xmlns="http://www.w3.org/2000/svg">
<path xmlns="http://www.w3.org/2000/svg" id="789" data-objid="0000X2" />
<admin xmlns="http://www.w3.org/2000/svg">
<g>
<path />
<path data-objid="0000X1" />
<path id="123" data-objid="0000X2" />
<path id="456" data-objid="0000X3" />
</g>
</admin>
<g xmlns="http://www.w3.org/2000/svg">
<path />
<path data-objid="0000X1" />
<path id="123" data-objid="0000X2" />
<path id="456" data-objid="0000X3" />
</g>
<path xmlns="http://www.w3.org/2000/svg" />
<path xmlns="http://www.w3.org/2000/svg" data-objid="0000X1" />
<path xmlns="http://www.w3.org/2000/svg" id="123" data-objid="0000X2" />
<path xmlns="http://www.w3.org/2000/svg" id="456" data-objid="0000X3" />
<g xmlns="http://www.w3.org/2000/svg">
<path />
<path data-objid="0000X1" />
<path id="789" data-objid="0000X2" />
<path id="abc" data-objid="0000X3" />
</g>
<path xmlns="http://www.w3.org/2000/svg" />
<path xmlns="http://www.w3.org/2000/svg" data-objid="0000X1" />
<path xmlns="http://www.w3.org/2000/svg" id="789" data-objid="0000X2" />
<path xmlns="http://www.w3.org/2000/svg" id="abc" data-objid="0000X3" />
<path xmlns="http://www.w3.org/2000/svg" />
<path xmlns="http://www.w3.org/2000/svg" id="abc" data-objid="0000X3" />
</svg>
instead of
<svg xmlns="http://www.w3.org/2000/svg">
<path id="789" data-objid="0000X2" />
<admin>
<g>
<path />
<path data-objid="0000X1" />
<path id="123" data-objid="0000X2" />
<path id="456" data-objid="0000X3" />
</g>
</admin>
<g>
<path />
<path data-objid="0000X1" />
<path id="123" data-objid="0000X2" />
<path id="456" data-objid="0000X3" />
</g>
<path />
<path data-objid="0000X1" />
<path id="123" data-objid="0000X2" />
<path id="456" data-objid="0000X3" />
<g>
<path />
<path data-objid="0000X1" />
<path id="789" data-objid="0000X2" />
<path id="abc" data-objid="0000X3" />
</g>
<path />
<path data-objid="0000X1" />
<path id="789" data-objid="0000X2" />
<path id="abc" data-objid="0000X3" />
<path />
<path id="abc" data-objid="0000X3" />
</svg>
What am I missing ? What do I do wrong ?
How to correct this without having to cast to varchar and then do a search-and-replace on "xmlns=..." ?