SQLXML : How to Merge Two Nodes Using FOR XML PATH

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
— ============================================================
— 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" />
*/

[/sql]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s