Advertisements
Home > XML > more examples on querying XML …with CROSS APPLY & XQuery – in SQL Server

more examples on querying XML …with CROSS APPLY & XQuery – in SQL Server


My quest to know more about XML and to use it in SQL language always encourages me to google this topic and religiously follow the MSDN’s SQL Server XML forum. I liked this post on XML, it contains some more ways to query XML data cited by some SQL experts.

In my previous posts I’ve shown some practical examples on how to query your XML data or string and get results in tabular format. Following are the links I’ve previously posted on this topic:

1. https://sqlwithmanoj.com/2011/01/28/select-an-xml-to-table/

2. https://sqlwithmanoj.com/2011/07/13/query-xml-string-in-tabular-format/

3. https://sqlwithmanoj.com/2011/07/13/select-or-query-nodes-in-hierarchial-or-nested-xml/
 

–> Let’s see one more example and multiple ways to query an XML string:

DECLARE @XML XML
SET @XML =
'<Input>
    <Courses>
        <Course>
            <Id>27</Id>
            <Students>
                <Id>19876</Id>
                <Id>19878</Id>
            </Students>
        </Course>
        <Course>
            <Id>29</Id>
            <Students>
                <Id>19879</Id>
            </Students>
        </Course>
    </Courses>
</Input>'
-> Desired Output: CourseId Students
27	 19876
27	 19878
29	 19879
-- Method# 1. Simple approach but bit costly | Query Cost: 83%
select t.c.value('../../Id[1]', 'INT') as CourseId,
	t.c.value('.', 'INT') as Students
from @XML.nodes('//Input/Courses/Course/Students/Id') as t(c)

-- Method# 2. By using Cross Apply | Query Cost: 17%
select t.c.value('Id[1]', 'INT') as CourseId,
	t1.c1.value('.', 'INT') as Students
from @XML.nodes('//Input/Courses/Course') as t(c)
cross apply t.c.nodes('Students/Id') as t1(c1)

The above 2 approaches shows that the second one with CROSS APPLY is much more performant.

– The traditional approach (#1) traverses the nodes (parent/child) and pulls the desired data.

– But the 2nd one with APPLY clause fetches specific node’s entire row and join it with the SELECTed data.
 

The NODES() function allows us to identify a particular node and map it into a new row. As the NODES function returns a rowset thus it can be queried by a SELECT statement or used like a UDF by applying APPLY clause. More on NODES(), here’s the link.
 

–> Let’s see an another approach by applying XQuery in a QUERY() function. The XQuery is a string, an XQuery expression, that queries for XML nodes such as elements, attributes, in an XML instance. More on QUERY(), here’s the link.

-- Method# 3. By using XML Query:

DECLARE @temp XML
set  @temp = @XML.query('
for $a in /Input/Courses/Course/Id,
    $b in /Input/Courses/Course/Students/Id
where $a/.. is $b/../..
return element detail {attribute CourseID {string($a)},attribute StudentID {string($b)}}')

select t.c.value('@CourseID','int') as [CourseID],
	t.c.value('@StudentID','int') as [StudentID]
from @temp.nodes('/detail') as t(c)

Will see some more examples & more stuff on XML in my forthcoming posts.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 


Advertisements
Categories: XML Tags: , , ,
  1. No comments yet.
  1. October 23, 2015 at 3:55 pm

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

%d bloggers like this: