-- ============================================================
-- sample code that shows how to merge different nodes
-- in the same XML document by using FOR XML PATH()
-- Donabel Santos
-- ============================================================
-- sample XML snippet
DECLARE @sampleXML XML
SET @sampleXML =
'
<Contacts>
 <Contact Type="Main" Value="John Doe">
 <Locations>
 <Location Type="Headquarters" Address="123 XYZ" City="New Westminster"
 Province="BC" Country="CA" PostalCode="V1L1B6" />
 </Locations>
 <Phones>
 <Phone Type="Main" Value="6041112222" />
 <Phone Type="Secondary" Value="6041113333" />
 <Phone Type="Fax" Value="6045553322" />
 </Phones>
 <Emails>
 <Email Type="Main" Value="[email protected]" />
 <Email Type="Secondary" Value="[email protected]" />
 </Emails>
 </Contact>
 <Contact Type="Secondary" Value="Mary Smith">
 <Locations>
 <Location Type="Headquarters" Address="123 ABC" City="New Westminster"
 Province="BC" Country="CA" PostalCode="V1L1B6" />
 </Locations>
 <Phones>
 <Phone Type="Main" Value="6041112255" />
 <Phone Type="Secondary" Value="6041113777" />
 <Phone Type="Fax" Value="6045553311" />
 </Phones>
 <Emails>
 <Email Type="Main" Value="[email protected]" />
 <Email Type="Secondary" Value="[email protected]" />
 </Emails>
 </Contact>
</Contacts>
'
-- get only the elements underneath <Phones> and <Emails>
SELECT
 @sampleXML.query ('(/Contacts/Contact/Phones/*)'),
 @sampleXML.query ('(/Contacts/Contact/Emails/*)')
FOR XML PATH('')
-- result
/*
<Phone Type="Main" Value="6041112222" />
<Phone Type="Secondary" Value="6041113333" />
<Phone Type="Fax" Value="6045553322" />
<Phone Type="Main" Value="6041112255" />
<Phone Type="Secondary" Value="6041113777" />
<Phone Type="Fax" Value="6045553311" />
<Email Type="Main" Value="[email protected]" />
<Email Type="Secondary" Value="[email protected]" />
<Email Type="Main" Value="[email protected]" />
<Email Type="Secondary" Value="[email protected]" />
*/
VN:F [1.9.22_1171]
Rating: 9.8/10 (6 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)
SQLXML : How to Merge Two Nodes Using FOR XML PATH, 9.8 out of 10 based on 6 ratings  
Be Sociable, Share!
  • Tweet

Related posts:

  1. SQLXML : How to Work With XML Elements (or Nodes) in SQL Server …
  2. SQLXML: Fun with SQLXML XQuery – Miscellaneous Functions – Part 1 …
  3. Order of Attribute-Centric Columns in FOR XML PATH matters …
  4. Valid SQLXML XSD Data Types, and Sample SQL Server XML Schemas …
  5. SQLXML : How to Join Multiple XML Snippets (using query() and UNION ALL) …
  6. SQLXML : How to Create an XML Schema …

YARPP