SQLXML : How to Join Multiple XML Snippets (using query() and UNION ALL)

Written by belle
Posted April 4, 2009 at 11:04 pm


-- ============================================================
-- sample code on joining multiple XML snippets
-- using query() and UNION ALL
-- Donabel Santos
-- ============================================================

-- declare xml variables
DECLARE @firstXMLSnippet XML
DECLARE @secondXMLSnippet XML

-- first XML snippet
SET  @firstXMLSnippet =
'
  <attributes>
    <attribute id="13" name="EmpNo">
      <item value="10" />
    </attribute>
    <attribute id="44" name="Position">
      <item value="Manager" />
    </attribute>
    <attribute id="32" name="Address">
      <item value="123 XYZ St." />
    </attribute>
    <attribute id="33" name="City">
      <item value="Vancouver" />
    </attribute>
  </attributes>
'

-- second XML snippet
SET  @secondXMLSnippet =
'
    <attribute id="37" name="Province">
      <item value="BC" />
    </attribute>
    <attribute id="52" name="Comment">
      <item value="SQL Server XML How To" />
    </attribute>
'

-- use query() to extract just the <attribute> elements
-- join using UNION ALL
-- add the root element back by using ROOT() option
SELECT  @firstXMLSnippet.query('//attribute')
UNION ALL
SELECT  @secondXMLSnippet
FOR XML PATH (''), ROOT('attributes'), TYPE

--result
/*
<attributes>
  <attribute id="13" name="EmpNo">
    <item value="10" />
  </attribute>
  <attribute id="44" name="Position">
    <item value="Manager" />
  </attribute>
  <attribute id="32" name="Address">
    <item value="123 XYZ St." />
  </attribute>
  <attribute id="33" name="City">
    <item value="Vancouver" />
  </attribute>
  <attribute id="37" name="Province">
    <item value="BC" />
  </attribute>
  <attribute id="52" name="Comment">
    <item value="SQL Server XML How To" />
  </attribute>
</attributes>
*/