Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

XQuery Lab 3 - Filtering specific nodes

Jun 27 2008 1:34AM by Jacob Sebastian   

Sometimes we need to apply a filter on the nodes of an XML document and retrieve only those nodes that meet a specific criteria. Most of the times, such filters will be applied on a given attribute value of the specific element. Here is such an example.

Here is the sample XML document that we have.

<root>
<Branch id="1">
<PeriodType pt="0">
<PeriodNumber pn="1">
<StartDate>06/23/2008</StartDate>
<EndDate>06/24/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="2">
<StartDate>06/16/2008</StartDate>
<EndDate>06/22/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="3">
<StartDate>06/09/2008</StartDate>
<EndDate>06/15/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="4">
<StartDate>06/02/2008</StartDate>
<EndDate>06/08/2008</EndDate>
</PeriodNumber>
</PeriodType>
<PeriodType pt="1">
<PeriodNumber pn="1">
<StartDate>06/23/2008</StartDate>
<EndDate>06/24/2008</EndDate>
</PeriodNumber>
</PeriodType>
</Branch>
</root>

We need to write a query that reads the StartDate and EndDate of all <PeriodType> elements where the "pt" attribute has a value of "1". This filter can be applied directly within the "nodes()" operator of the XML data type. Here is the expected result.

/*
StartDate EndDate
----------------------- -----------------------
2008-06-23 00:00:00.000 2008-06-24 00:00:00.000
*/

The query should return only one row, because there is only one XML element that where the "pt" attribute of <PeriodType> is "1". Here is the query that performs this.

DECLARE @dates xml

SET @dates = '
<root>
<Branch id="1">
<PeriodType pt="0">
<PeriodNumber pn="1">
<StartDate>06/23/2008</StartDate>
<EndDate>06/24/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="2">
<StartDate>06/16/2008</StartDate>
<EndDate>06/22/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="3">
<StartDate>06/09/2008</StartDate>
<EndDate>06/15/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="4">
<StartDate>06/02/2008</StartDate>
<EndDate>06/08/2008</EndDate>
</PeriodNumber>
</PeriodType>
<PeriodType pt="1">
<PeriodNumber pn="1">
<StartDate>06/23/2008</StartDate>
<EndDate>06/24/2008</EndDate>
</PeriodNumber>
</PeriodType>
</Branch>
</root>'


SELECT
x.value('StartDate[1]','DATETIME') AS StartDate,
x.value('EndDate[1]','DATETIME') AS EndDate
FROM @dates.nodes('/root/Branch/PeriodType[@pt="1"]/PeriodNumber') d(x)

/*
StartDate EndDate
----------------------- -----------------------
2008-06-23 00:00:00.000 2008-06-24 00:00:00.000
*/

XQuery Labs - A Collection of XQuery Sample Scripts

Tags: XQuery-Labs, XML, XQuery, XQuery Functions, SQL Server XQuery, XQuery in TSQL, XQuery Training, XQuery Tutorial,


Jacob Sebastian
1 · 100% · 32235
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"XQuery Lab 3 - Filtering specific nodes" rated 5 out of 5 by 1 readers
XQuery Lab 3 - Filtering specific nodes , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]