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 14 - Applying filters on multiple attributes

Aug 6 2008 12:43AM by Jacob Sebastian   

 

In one of the previous labs, we saw an example that select specific nodes by applying a filter on the value of an attribute. Some times, it can happen that you need to apply filters on more than one  attribute to retrieve a set of nodes matching a given criteria. This post shows an example that applies filters on 2 attributes. 

Here is the sample data.

<Employees>
<Employee id="123" dept="IT" type="Permanent">
<Name first="Jacob" middle="V" last="Sebastian"/>
</Employee>
<Employee id="234" dept="IT" type="Temporary">
<Name first="Steve" middle="K" last="Austine"/>
</Employee>
<Employee id="345" dept="OP" type="Permanent">
<Name first="Smith" middle="R" last="Wills"/>
</Employee>
</Employees>

Assume that we need to run two queries on this XML. The first query should return all employees from IT department. This should return two rows. The second query should return all the permanent employees from IT department. This should return only one row.

Let us write the first query. Let us apply a filter on the "dept" attribute.

DECLARE @x XML
SELECT @x = '
<Employees>
<Employee id="123" dept="IT" type="Permanent">
<Name first="Jacob" middle="V" last="Sebastian"/>
</Employee>
<Employee id="234" dept="IT" type="Temporary">
<Name first="Steve" middle="K" last="Austine"/>
</Employee>
<Employee id="345" dept="OP" type="Permanent">
<Name first="Smith" middle="R" last="Wills"/>
</Employee>
</Employees>'


SELECT
e.value('@first[1]','VARCHAR(10)') AS FirstName,
e.value('@middle[1]','VARCHAR(10)') AS MiddleName,
e.value('@last[1]','VARCHAR(10)') AS LastName
FROM @x.nodes('/Employees/Employee[@dept="IT"]/Name') x(e)
/*
FirstName MiddleName LastName
---------- ---------- ----------
Jacob V Sebastian
Steve K Austine
*/

The following query also produces the same result. Note the changes in in the expression used with the "nodes()" method.

DECLARE @x XML
SELECT @x = '
<Employees>
<Employee id="123" dept="IT" type="Permanent">
<Name first="Jacob" middle="V" last="Sebastian"/>
</Employee>
<Employee id="234" dept="IT" type="Temporary">
<Name first="Steve" middle="K" last="Austine"/>
</Employee>
<Employee id="345" dept="OP" type="Permanent">
<Name first="Smith" middle="R" last="Wills"/>
</Employee>
</Employees>'


SELECT
e.value('@first[1]','VARCHAR(10)') AS FirstName,
e.value('@middle[1]','VARCHAR(10)') AS MiddleName,
e.value('@last[1]','VARCHAR(10)') AS LastName
FROM @x.nodes('//Employee[@dept="IT"]/*') x(e)
/*
FirstName MiddleName LastName
---------- ---------- ----------
Jacob V Sebastian
Steve K Austine
*/

Now let us write the second query. Let us add two filters: one on the "dept" attribute and the other on the "type" attribute.

DECLARE @x XML
SELECT @x = '
<Employees>
<Employee id="123" dept="IT" type="Permanent">
<Name first="Jacob" middle="V" last="Sebastian"/>
</Employee>
<Employee id="234" dept="IT" type="Temporary">
<Name first="Steve" middle="K" last="Austine"/>
</Employee>
<Employee id="345" dept="OP" type="Permanent">
<Name first="Smith" middle="R" last="Wills"/>
</Employee>
</Employees>'


SELECT
e.value('@first[1]','VARCHAR(10)') AS FirstName,
e.value('@middle[1]','VARCHAR(10)') AS MiddleName,
e.value('@last[1]','VARCHAR(10)') AS LastName
FROM @x.nodes('//Employee[@dept="IT"][@type="Permanent"]/*') x(e)
/*
FirstName MiddleName LastName
---------- ---------- ----------
Jacob V Sebastian
*/

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% · 32225
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • How do I XQuery abc based on field1? field1 text must not be hardcoded and should come as a part of the XQuery.

    <tst:tag>
      <tst:node tst:name="field1">abc</tst:node> 
      <tst:node tst:name="field2">def</tst:node> 
      <tst:node tst:name="field3">g</tst:node> 
      <tst:node tst:name="field4">hij</tst:node> 
      <tst:node tst:name="field5">klm</tst:node> 
      <tst:node tst:name="field6">nop</tst:node> 
      <tst:node tst:name="field7">hij</tst:node> 
      <tst:node tst:name="field8">qrs</tst:node> 
     </tst:tag>
    
    commented on Oct 4 2012 4:15AM
    banavalikar
    1533 · 0% · 13
  • I have answered your question in this thread. Please take a look at it.

    commented on Oct 5 2012 8:15AM
    Jacob Sebastian
    1 · 100% · 32225

Your Comment


Sign Up or Login to post a comment.

"XQuery Lab 14 - Applying filters on multiple attributes" rated 5 out of 5 by 1 readers
XQuery Lab 14 - Applying filters on multiple attributes , 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]