SQLXML : How to Merge Two Nodes Using FOR XML PATH

Written by belle
Posted April 9, 2009 at 11:29 pm

-- ============================================================
-- 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="john.doe@email.ca" />
 <Email Type="Secondary" Value="contact@email.ca" />
 </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="mary.smith@email.ca" />
 <Email Type="Secondary" Value="contact@email.ca" />
 </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="john.doe@email.ca" />
<Email Type="Secondary" Value="contact@email.ca" />
<Email Type="Main" Value="mary.smith@email.ca" />
<Email Type="Secondary" Value="contact@email.ca" />
*/