-- ============================================================
-- 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>
*/