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

September 22, 2011 Leave a comment Go to comments

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.
 


Advertisement
Categories: XML Tags: , , ,
  1. Heron Carlos
    February 15, 2018 at 10:53 pm

    Hello Mr. Manoj Pandey

    Thanks a lot for explanations about xml queries.

    I have a question, can you help me fix it?

    I get 2 xml files, but one of them has fewer nodes.

    How can I select the node <item tag = “Ownership Type” and get the value = “CCC” of each file.

    I’m with the query below.

    First File :

    Second File :

    WITH XMLNAMESPACES (‘http://www.xxxxx.ch/service/vci/2010/11/16’ as fix )
    SELECT
    vc.[Vessel_Name]
    ,vc.[Voyage_Number]
    ,vc.[Port_Name]
    ,vc.[Port_UN]
    ,vc.[Terminal_Name]
    ,vc.[Terminal_Code]
    ,CASE
    when ISDATE( n.c.value(‘@value[1]’,’VARCHAR(60)’))=1 THEN n.c.value(‘@value[1]’,’datetime’) ELSE NULL END as “Operations completed”
    ,CASE
    when ISDATE( m.c.value(‘@value[1]’,’VARCHAR(60)’))=1 THEN m.c.value(‘@value[1]’,’datetime’) ELSE NULL END as “Gangway Down”
    ,m.c.value(‘@value[1]’,’datetime’) as “Gangway Down”
    ————————————–
    i need the value from node item tag= “Ownership Type” , “CCC”
    —————————————
    from
    [Inter].[dbo].[VCI_Info] vc
    outer apply VCI_Data.nodes(‘/fix:vci/fix:PVE/fix:item[38]’) as m(c) — “Gangway Down”
    outer apply VCI_Data.nodes(‘/fix:vci/fix:PVE/fix:item[43]’) as n(c) — “Operations completed”
    — “Ownership Type”

  2. Heron Carlos
    February 15, 2018 at 11:14 pm

    the xml files :

    First File

    Second File

  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 )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: