Output xml and relational data in OPENXML

This question arose from a post in SQL server forum. Due to performance reason, it is required to use OPENXML to process a big xml documnet. But it also needs to return a specific xml document for each record.
The source xml is as @xmlData in the following sample code;
The expected ouput is as:
First record:
Camry  
It is Camry
<ns:Features xmlns:ns="http://xyz.com"><ns:Feature>Full wheel drive.</ns:Feature><ns:Feature>ABS</ns:Feature><ns:Feature>PW,PD</ns:Feature><ns:Feature>GPS</ns:Feature><ns:Feature>AC</ns:Feature></ns:Features>
 
We can combine OPENXML and xquery to achieve this.
OPENXML output meta data @mp:xmltext, which is the textual serialization of the element and its attributes, and also the subelements, as used in the overflow handling of OPENXML, i.e. it is the piece of xml that under the specified xpath in the second parameter in OPENXML. We can use xquery methods like .query on it.
 

DECLARE @xmlData XML;

set

@xmlData = ‘<?xml version="1.0"?>

<Root xmlns="http://xyz.com"&gt;

<Items>

<Item>

<Name>Camry</Name>

<Description>It is Camry</Description>

<ItemProperties>

<Rank>High</Rank>

<Color>Red</Color>

<Feature>Full wheel drive.</Feature>

<Feature>ABS</Feature>

<Feature>PW,PD</Feature>

<Feature>GPS</Feature>

<Feature>AC</Feature>

<Category>Car</Category>

<Year>2009</Year>

</ItemProperties>

</Item>

</Items>

<Items>

<Item>

<Name>Civic</Name>

<Description>It is Civic</Description>

<ItemProperties>

<Rank>medium</Rank>

<Color>Green</Color>

<Feature>ABS</Feature>

<Feature>PW,PD</Feature>

<Feature>GPS</Feature>

<Feature>AC</Feature>

<Category>Car</Category>

<Year>2010</Year>

</ItemProperties>

</Item>

</Items>

</Root>’;

Declare

@iDoc int

EXEC sp_xml_preparedocument @iDoc Output,@xmlData,‘<Root xmlns:ns="http://xyz.com"/>&#8217;

SELECT D.[Name],D.[Description],

— Construct the <Features> xml document by .query:

D

.Features.query(N

declare namespace ns="http://xyz.com&quot;;

<ns:Features>

{

for $Feature in ns:Item/ns:ItemProperties/ns:Feature

return $Feature

}

</ns:Features>

) AS Features

From OPENXML(@iDoc,‘ns:Root/ns:Items/ns:Item’,3)

With

(

[Name]

nvarchar(50) ‘ns:Name’,

[Description]

nvarchar(255) ‘ns:Description’,

Features

xml ‘@mp:xmltext’

) D

 

Exec sp_xml_removedocument @iDoc

 

Advertisements
This entry was posted in XML, XQuery and XPath. Bookmark the permalink.

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