T-SQL supports the subset of XQuery language that
is used for querying the xml data type. XQuery is a language that can query structured
or semi-structured XML data. With the
xml data type support provided in the database engine, documents can be stored
in a database and then queried by using XQuery.
To query an
XML instance stored in a variable or column of xml type, we use the xml data
type methods.
For example
we can declare a variable of xml type and query it by using the query () method of the xml data type.
Example:
DECLARE @x XML
SET @x = '<ROOT><a>111</a></ROOT>'
SELECT @x.query('/ROOT/a') AS ResultSet
ResumtSet
---------
<a>111</a>
SQL Server 2005 XQuery Function
The following
4 functions are the XQuery function available in SQL Server 2005.
xml.exist
This methods
returns a Boolean values based upon a search expression on an XML node.
SELECT @x.exist('/christmaslist/person[@gift = "socks"]')
SELECT @x. exist ('/christmaslist/zach')
SELECT @x.exist('/christmaslist/person[@gift = "Socks"]')
xml.value
This method
accepts XQuery statements and returns a single value.
SELECT @x.value('/christmaslist[1]/person[1]/@name', 'VARCHAR(20)')
SELECT @x.value('/christmaslist[1]/person[2]/@name', 'VARCHAR(20)')
xml.query
This method
accepts an XQuery statement and returns an instance of the XML data type.
SELECT @x.query('/christmaslist/person')
Which will returns
the xml documents
<person name="betty"
gift="camera" />
<person name="zach"
gift="elmo doll" />
<person name="brad" gift="socks"
/>
xml.nodes
This method
is very useful when we need to shred the data from an XML data type variable
into relational data. This method accepts an XQuery statement as a parameter
and returns a row set that contains logical scalar data from the XML variable.
This
following example is from a rules engine I am creating. The XML itself
represents a rule, and the XQuery below parses the rule into a table. The
beauty of the XML in this case is the extensible and the portability.
Meaning, I do not have to define all the elements if I don’t want to, or, I
could add additional elements with ease. Then they are also easy to transfer
because XML is a relational model in itself.
The XML File
<rule id="100100001">
<conditions>
<filter type="and">
<condition
module="person" attribute="age" operator="gt">
<value>35</value>
</condition>
<condition
module="transportation" attribute="automobile" operator="neq">
<value>truck</value>
</condition>
<condition
module="family" attribute="spouse" operator="eq">
<value>wife</value>
<value>child</value>
</condition>
<condition
module="job" attribute="description" operator="eq">
<value>receptionist</value>
<value>sales</value>
</condition>
</filter>
</conditions>
</rule>
Now How I Process It
DECLARE @RuleXML XML
SET @RuleXML =
'<rule
id="100100001">
<conditions>
<filter type="and">
<condition module="person"
attribute="age" operator="gt">
<value>35</value>
</condition>
<condition
module="transportation" attribute="automobile"
operator="neq">
<value>truck</value>
</condition>
<condition module="family"
attribute="spouse" operator="eq">
<value>wife</value>
<value>child</value>
</condition>
<condition module="job"
attribute="description" operator="eq">
<value>receptionist</value>
<value>sales</value>
</condition>
</filter>
</conditions>
</rule>'
SELECT
Attribute = N.c.value('(../@attribute)', 'nvarchar(4000)')
,Value = N.c.value('(.)', 'nvarchar(4000)')
,Operator = N.c.value('(../@operator)', 'nvarchar(4000)')
FROM @RuleXML.nodes('/rule/conditions/filter/condition/value') AS N(c)
WHERE N.c.value('(.)', 'nvarchar(4000)') != ''
Output
Attribute Value Operator
--------- ------ ---------
age 35 gt
automobile truck neq
spouse wife eq
spouse child eq
description receptionist eq
description sales eq
Running this self-contained example, we
see that we return a table of Attribute, Values, and Operators. These would
then be used in order to further output a result set for the rule.
The most important line above is the
FROM clause. It defines the path for the actual values we are returning. These
values can have multiple rows of output, and in order to return these multiple
rows, you need this line to drill down to the most granular element. From
there, look at the select list. The select list contains operators that
traverse the path upwards. We’ll notice this with the ../ which is the same as
saying “go back one directory”. From here you can return the attributes by
precluding them with the at (@) symbol.
Hope you like it.
Posted by: MR. JOYDEEP DAS
No comments:
Post a Comment