How can I merge several XML-documents into one without namespace accumulation?

Viewed 102

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=..." ?

1 Answers
Related