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

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

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

[/sql]

Advertisements

One comment

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