Shred complex XML by XQuery node() and value() methods

 /*

XQuery nodes() method allows you to identify nodes that will be mapped into a new row. Its result is a rowset that contains logical copies of the original XML instances.

XQuery has value() method that can be used to get attributes and elements value. By combining value() and node() method, we can shred an xml document into relational data.

The general sytax of node() method is: nodes (XQuery) as Table(Column)

where XQuery is an XQuery expression; Table(Column) is the table name and the column name for the resulting rowset.

*/

USE AdventureWorks

GO

DECLARE @Xml xml (Production.ProductDescriptionSchemaCollection)

SELECT

@Xml=CatalogDescription FROM Production.ProductModel WHERE ProductModelID=19

— The document of @Xml is attached in the end of this article.

— To show xml in the rowset:

select

T.r.query(‘.’) FROM @Xml.nodes(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; /p:ProductDescription/p:Specifications’) as T(r)

/* Use value() method to shred the xml into relational data. Note we need to use xquery expression "/p:ProductDescription[1]/p:Specifications[1]/text()[1]" in order to get the content of the element "Specifications" */

SELECT

@Xml

.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; /p:ProductDescription[1]/@ProductModelName’,‘nvarchar(100)’) as ProductModelName,

@Xml.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; /p:ProductDescription[1]/p:Specifications[1]/text()[1]’,‘nvarchar(50)’) as Specifications,

T

.r.value(‘Material[1]’,‘nvarchar(50)’) as Material,

T

.r.value(‘Color[1]’,‘nvarchar(50)’) as Color,

T

.r.value(‘ProductLine[1]’,‘nvarchar(50)’) as ProductLine,

T

.r.value(‘Style[1]’,‘nvarchar(50)’) as Style

FROM

@Xml.nodes(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

/p:ProductDescription/p:Specifications’

) as T(r)

/*

We can also use node() to return the whole xml one node and shred more elements. Note if an xPath contains more than one namespace, we need declare all of them in the xquery expression.

*/

DECLARE @Xml xml (Production.ProductDescriptionSchemaCollection)

SELECT

@Xml=CatalogDescription FROM Production.ProductModel WHERE ProductModelID=19

select

T.r.query(‘.’) FROM @Xml.nodes(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; /p:ProductDescription’) as T(r)

SELECT

T

.r.value(‘@ProductModelID’,‘nvarchar(20)’) as ProductModelID,

T

.r.value(‘@ProductModelName’,‘nvarchar(100)’) as ProductModelName,

— Product Features

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Features[1]/text()[1]’,‘nvarchar(50)’) as Features,

T.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; ./p:Features[1]/wf:wheel[1]’

,‘nvarchar(100)’) as Feature_Wheel,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; ./p:Features[1]/wf:BikeFrame[1]’

,‘nvarchar(200)’) as Feature_BikeFrame,

— Product Warranty

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; ./p:Features[1]/wm:Warranty[1]/wm:WarrantyPeriod[1]’

,‘nvarchar(50)’) as Warranty_Period,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; ./p:Features[1]/wm:Warranty[1]/wm:Description[1]’

,‘nvarchar(100)’) as Warranty_Description,

— Product Maintnance

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; ./p:Features[1]/wm:Maintenance[1]/wm:NoOfYears[1]’

,‘nvarchar(50)’) as Maintenance_Period,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; ./p:Features[1]/wm:Maintenance[1]/wm:Description[1]’

,‘nvarchar(200)’) as Maintenance_Description,

— Product Picture

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Picture[1]/p:Angle[1]’,‘nvarchar(50)’) as Picture_Angle,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Picture[1]/p:Size[1]’,‘nvarchar(50)’) as Picture_Size,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Picture[1]/p:ProductPhotoID[1]’,‘nvarchar(50)’) as Picture_ProductPhotoID,

— Product Specs

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Specifications[1]/text()[1]’,‘nvarchar(50)’) as Specifications,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Specifications[1]/Material[1]’,‘nvarchar(50)’) as Material,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Specifications[1]/Color[1]’,‘nvarchar(50)’) as Color,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Specifications[1]/ProductLine[1]’,‘nvarchar(50)’) as ProductLine,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Specifications[1]/Style[1]’,‘nvarchar(50)’) as Style

FROM

@Xml.nodes(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

/p:ProductDescription’

) as T(r)

 

/*

We can use the above query on the table Production.ProductModel to get a record set for all the products.

In this case, we need to use CROSS APPLY operator.

APPLY operator allows you to invoke a table-valued function ( here is rowset returned by node() xquery method) for each row returned by an outer table expression of a query.

The table-valued function acts as the right input and the outer table expression acts as the left input.

There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

*/

SELECT

T

.r.value(‘@ProductModelID’,‘nvarchar(20)’) as ProductModelID,

T

.r.value(‘@ProductModelName’,‘nvarchar(100)’) as ProductModelName,

— Product Features

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Features[1]/text()[1]’,‘nvarchar(50)’) as Features,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; ./p:Features[1]/wf:wheel[1]’

,‘nvarchar(100)’) as Feature_Wheel,

T.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; ./p:Features[1]/wf:BikeFrame[1]’,‘nvarchar(200)’) as Feature_BikeFrame,

— Product Warranty

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; ./p:Features[1]/wm:Warranty[1]/wm:WarrantyPeriod[1]’

,‘nvarchar(50)’) as Warranty_Period,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; ./p:Features[1]/wm:Warranty[1]/wm:Description[1]’

,‘nvarchar(100)’) as Warranty_Description,

— Product Maintnance

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; ./p:Features[1]/wm:Maintenance[1]/wm:NoOfYears[1]’

,‘nvarchar(50)’) as Maintenance_Period,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; ./p:Features[1]/wm:Maintenance[1]/wm:Description[1]’

,‘nvarchar(200)’) as Maintenance_Description,

— Product Picture

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Picture[1]/p:Angle[1]’,‘nvarchar(50)’) as Picture_Angle,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Picture[1]/p:Size[1]’,‘nvarchar(50)’) as Picture_Size,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Picture[1]/p:ProductPhotoID[1]’,‘nvarchar(50)’) as Picture_ProductPhotoID,

— Product Specs

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Specifications[1]/text()[1]’,‘nvarchar(50)’) as Specifications,

T.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Specifications[1]/Material[1]’,‘nvarchar(50)’) as Material,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Specifications[1]/Color[1]’,‘nvarchar(50)’) as Color,

T.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Specifications[1]/ProductLine[1]’,‘nvarchar(50)’) as ProductLine,

T

.r.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; ./p:Specifications[1]/Style[1]’,‘nvarchar(50)’) as Style

FROM

Production.ProductModel CROSS APPLY CatalogDescription.nodes(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

/p:ProductDescription’

) as T(r)

WHERE

CatalogDescription IS NOT NULL

The xml document for ProductModeID=19:

<?xml-stylesheet href="ProductDescription.xsl" type="text/xsl"?>

<

p1:ProductDescription xmlns:p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription" xmlns:wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" xmlns:wf="http://www.adventure-works.com/schemas/OtherFeatures" xmlns:html="http://www.w3.org/1999/xhtml" ProductModelID="19" ProductModelName="Mountain 100">

<

p1:Summary>

<

html:p>Our top-of-the-line competition mountain bike.

Performance-enhancing options include the innovative HL Frame,

super-smooth front suspension, and traction for all terrain.

</

html:p>

</p1:Summary>

<

p1:Manufacturer>

<

p1:Name>AdventureWorks</p1:Name>

<

p1:Copyright>2002</p1:Copyright>

<

p1:ProductURL>http://www.Adventure-works.com</p1:ProductURL>

</

p1:Manufacturer>

<

p1:Features>These are the product highlights.

<

wm:Warranty><wm:WarrantyPeriod>3 years</wm:WarrantyPeriod><wm:Description>parts and labor</wm:Description></wm:Warranty><wm:Maintenance><wm:NoOfYears>10 years</wm:NoOfYears><wm:Description>maintenance contract available through your dealer or any AdventureWorks retail store.</wm:Description></wm:Maintenance><wf:wheel>High performance wheels.</wf:wheel><wf:saddle><html:i>Anatomic design</html:i> and made from durable leather for a full-day of riding in comfort.</wf:saddle><wf:pedal><html:b>Top-of-the-line</html:b> clipless pedals with adjustable tension.</wf:pedal><wf:BikeFrame>Each frame is hand-crafted in our Bothell facility to the optimum diameter

and wall-thickness required of a premium mountain frame.

The heat-treated welded aluminum frame has a larger diameter tube that absorbs the bumps.

</wf:BikeFrame><wf:crankset> Triple crankset; alumunim crank arm; flawless shifting. </wf:crankset></p1:Features>

<!–

add one or more of these elements… one for each specific product in this product model –>

<

p1:Picture>

<p1:Angle>front</p1:Angle>

<

p1:Size>small</p1:Size>

<

p1:ProductPhotoID>118</p1:ProductPhotoID>

</

p1:Picture>

<!–

add any tags in <specifications> –>

<

p1:Specifications> These are the product specifications.

<

Material>Almuminum Alloy</Material><Color>Available in most colors</Color><ProductLine>Mountain bike</ProductLine><Style>Unisex</Style><RiderExperience>Advanced to Professional riders</RiderExperience></p1:Specifications>

</

p1:ProductDescription>

 

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