Apr 24, 2014

XQuery In sql server with examples

Xquery is way to get the desire data from XML. Here we will discuss how to get data from XML by examples.
It has three sections:

a. XML data is stored in a variable and XML is attribute centric format
b. XML data is stored in a variable and XML is element centric format
c. XML data is stored in a column of table

Xquery when XML data is stored in a variable and XML is attribute centric format:

DECLARE @xlStudentAttributeAttributeAS XML =
      '<Student>
        <Students RollNo="300" Name="Scott" />
        <Students RollNo="200" Name="Davis" />
        <Students RollNo="303" Name="Nancy" />
      </Student>'

1. Xquery to get the all child tags of  tag Student is single row:

SELECT @xlStudentAttribute.query('Student/Students')

Output:

<Students RollNo="300" Name="Scott" />
<Students RollNo="200" Name="Davis" />
<Students RollNo="303" Name="Nancy" />

2. Xquery to get the all child tags of tag Student in different rows:

SELECT T.C.query('.') AS result
FROM @xlStudentAttribute.nodes('Student/Students') T(C)

Output:
<Students RollNo="300" Name="Scott" />
<Students RollNo="200" Name="Davis" />
<Students RollNo="303" Name="Nancy" />

3. Xquery to get the all child tags of  tag Student where RollNo is 300 in single row:

SELECT @xlStudentAttribute.query('Student/Students[@RollNo = 300]')

Output:

<Students RollNo="300" Name="Scott" />

4. Xquery to get the all child tags of  tag Student where RollNo greater than equal to 300 in multiple rows:

SELECT      T.C.query('.') AS result
FROM @xlStudentAttribute.nodes('Student/Students[@RollNo >= 300]') T(C)

Output:

<Students RollNo="300" Name="Scott" />
<Students RollNo="303" Name="Nancy" />

5. Xquery to get the all Name and RollNo of all students:

SELECT   
     Student.C.value('@RollNo','INT') AS RollNo,
     Student.C.value('@Name','VARCHAR(50)') AS Name
FROM @xlStudentAttribute.nodes('Student/Students') Student(C)

Output:

RollNo
Name
300
Scott
200
Davis
303
Nancy

6. Xquery to get the  Name and RollNo of all students where name is Davis:

SELECT     
      Student.C.value('@RollNo','INT') AS RollNo,
      Student.C.value('@Name','VARCHAR(50)') AS Name
FROM @xlStudentAttribute.nodes('Student/Students[@Name="Davis"]') Student(C)

Or

SELECT     
      Student.C.value('@RollNo','INT') AS RollNo,
      Student.C.value('@Name','VARCHAR(50)') AS Name
FROM @xlStudentAttribute.nodes('Student/Students') Student(C)
WHERE Student.C.value('@Name','VARCHAR(50)') = 'Davis'

Output:

RollNo
Name
200
Davis

7. Xquery to get the all child tags of tag Student order Name:

SELECT T.C.query('.') AS result
FROM @xlStudentAttribute.nodes('Student/Students') T(C)
ORDER BY T.C.value('@Name','VARCHAR(50)')

Output:

<Students RollNo="200" Name="Davis" />
<Students RollNo="303" Name="Nancy" />
<Students RollNo="300" Name="Scott" />

Xquery when XML data is stored in a variable and XML is element centric format:

DECLARE @xlStudentEelement AS XML =
      '<Student>
        <Students>
            <RollNo>300</RollNo>
            <Name>Scott</Name>
        </Students>
        <Students>
            <RollNo>200</RollNo>
            <Name>Davis</Name>
        </Students>
        <Students>
            <RollNo>303</RollNo>
            <Name>Nancy</Name>
        </Students>
      </Student>'

1. Xquery to get the all child tags of  tag Student is single row:

SELECT @xlStudentEelement.query('Student/Students')

Output:

<Students>
  <RollNo>300</RollNo>
  <Name>Scott</Name>
</Students>
<Students>
  <RollNo>200</RollNo>
  <Name>Davis</Name>
</Students>
<Students>
  <RollNo>303</RollNo>
  <Name>Nancy</Name>
</Students>

2. Xquery to get the all child tags of tag Student in different rows:

SELECT T.C.query('.') AS result
FROM @xlStudentEelement.nodes('Student/Students') T(C)

Output:

<Students>
  <RollNo>300</RollNo>
  <Name>Scott</Name>
</Students>
<Students>
  <RollNo>200</RollNo>
  <Name>Davis</Name>
</Students>
<Students>
  <RollNo>303</RollNo>
  <Name>Nancy</Name>
</Students>

3. Xquery to get the all child tags of  tag Student where RollNo is 300 in single row:

SELECT @xlStudentEelement.query('Student/Students[RollNo = 300]')

Output:

<Students>
  <RollNo>300</RollNo>
  <Name>Scott</Name>
</Students>

4. Xquery to get the all child tags of  tag Student where RollNo greater than equal to 300 in multiple rows:

SELECT      T.C.query('.') AS result
FROM @xlStudentEelement.nodes('Student/Students[RollNo >= 300]') T(C)

Output:

<Students>
  <RollNo>300</RollNo>
  <Name>Scott</Name>
</Students>
<Students>
  <RollNo>303</RollNo>
  <Name>Nancy</Name>
</Students>

5. Xquery to get the all Name and RollNo of all students:

SELECT     
      Student.C.value('RollNo[1]','INT') AS RollNo,
      Student.C.value('Name[1]','VARCHAR(50)') AS Name
FROM @xlStudentEelement.nodes('Student/Students') Student(C)

Output:

RollNo
Name
300
Scott
200
Davis
303
Nancy

6. Xquery to get the  Name and RollNo of all students where name is Davis:

SELECT     
      Student.C.value('RollNo[1]','INT') AS RollNo,
      Student.C.value('Name[1]','VARCHAR(50)') AS Name
FROM @xlStudentEelement.nodes('Student/Students[Name="Davis"]') Student(C)

Or

SELECT     
      Student.C.value('RollNo[1]','INT') AS RollNo,
      Student.C.value('Name[1]','VARCHAR(50)') AS Name
FROM @xlStudentEelement.nodes('Student/Students') Student(C)
WHERE Student.C.value('Name[1]','VARCHAR(50)') = 'Davis'

Output:

RollNo
Name
200
Davis

7. Xquery to get the all child tags of tag Student order Name:

SELECT T.C.query('.') AS result
FROM @xlStudentEelement.nodes('Student/Students') T(C)
ORDER BY T.C.value('Name[1]','VARCHAR(50)')

Output:

<Students>
  <RollNo>200</RollNo>
  <Name>Davis</Name>
</Students>
<Students>
  <RollNo>303</RollNo>
  <Name>Nancy</Name>
</Students>
<Students>
  <RollNo>300</RollNo>
  <Name>Scott</Name>
</Students>

Xquery when XML data is stored in a column of table:

CREATE TABLE tblSchool(
      ntSchoolID BIGINT PRIMARY KEY IDENTITY,
      vcSchoolName VARCHAR(50),
      xlStudent XML
)

INSERT INTO tblSchool
SELECT 'NVN',
      '<Student>
        <Students RollNo="300" Name="Scott" />
        <Students RollNo="200" Name="Davis" />
        <Students RollNo="303" Name="Nancy" />
      </Student>'
     
UNION ALL

SELECT 'DAV',
      '<Student>
        <Students RollNo="300" Name="Alain" />
        <Students RollNo="250" Name="Tim" />
        <Students RollNo="400" Name="Nancy" />
      </Student>'
   

1. Xquery to get the all child tags of  tag Student is single row:

SELECT
      ntSchoolID,
      xlStudent.query('Student/Students') AS Student
FROM tblSchool
   
Output:

ntSchoolID
Student
1
<Students RollNo="300" Name="Scott" />
<Students RollNo="200" Name="Davis" />
<Students RollNo="303" Name="Nancy" />
2
<Students RollNo="300" Name="Alain" />
<Students RollNo="250" Name="Tim" />
<Students RollNo="400" Name="Nancy" />


2. Xquery to get the all child tags of tag Student in different rows:

SELECT
      ntSchoolID,
      T.C.query('.') AS Student
FROM tblSchool S CROSS APPLY
xlStudent.nodes('Student/Students') T(C)

Output:

ntSchoolID
Student
1
<Students RollNo="300" Name="Scott" />
1
<Students RollNo="200" Name="Davis" />
1
<Students RollNo="303" Name="Nancy" />
2
<Students RollNo="300" Name="Alain" />
2
<Students RollNo="250" Name="Tim" />
2
<Students RollNo="400" Name="Nancy" />

3. Xquery to get the all child tags of  tag Student where RollNo is 300 in single row:

SELECT
      ntSchoolID,
      xlStudent.query('Student/Students[@RollNo = 300]') AS Student
FROM tblSchool

Output:

ntSchoolID
Student
1
<Students RollNo="300" Name="Scott" />
2
<Students RollNo="300" Name="Alain" />

4. Xquery to get the all child tags of  tag Student where RollNo greater than equal to 300 in multiple rows:

SELECT
      ntSchoolID,
      T.C.query('.') AS Student
FROM tblSchool S CROSS APPLY
xlStudent.nodes('Student/Students[@RollNo >= 300]') T(C)

Output:

ntSchoolID
Student
1
<Students RollNo="300" Name="Scott" />
1
<Students RollNo="303" Name="Nancy" />
2
<Students RollNo="300" Name="Alain" />
2
<Students RollNo="400" Name="Nancy" />

5. Xquery to get the all Name and RollNo of all students:

SELECT
      ntSchoolID,
      Student.C.value('@RollNo','INT') AS RollNo,
      Student.C.value('@Name','VARCHAR(50)') AS Name
FROM tblSchool S CROSS APPLY
xlStudent.nodes('Student/Students') Student(C)

Output:

ntSchoolID
RollNo
Name
1
300
Scott
1
200
Davis
1
303
Nancy
2
300
Alain
2
250
Tim
2
400
Nancy

6. Xquery to get the  Name and RollNo of all students where name is Davis:

SELECT
      ntSchoolID,
      Student.C.value('@RollNo','INT') AS RollNo,
      Student.C.value('@Name','VARCHAR(50)') AS Name
FROM tblSchool S CROSS APPLY
xlStudent.nodes('Student/Students[@Name="Davis"]') Student(C)

Or

SELECT
      ntSchoolID,
      Student.C.value('@RollNo','INT') AS RollNo,
      Student.C.value('@Name','VARCHAR(50)') AS Name
FROM tblSchool S CROSS APPLY
xlStudent.nodes('Student/Students') Student(C)
WHERE Student.C.value('@Name','VARCHAR(50)') = 'Davis'

Output:

ntSchoolID
RollNo
Name
1
200
Davis

7. Xquery to get the all child tags of tag Student order Name:

SELECT
      ntSchoolID,
      T.C.query('.') AS Student
FROM tblSchool S CROSS APPLY
xlStudent.nodes('Student/Students') T(C)
ORDER BY T.C.value('@Name','VARCHAR(50)')

Output:

ntSchoolID
Student
2
<Students RollNo="300" Name="Alain" />
1
<Students RollNo="200" Name="Davis" />
1
<Students RollNo="303" Name="Nancy" />
2
<Students RollNo="400" Name="Nancy" />
1
<Students RollNo="300" Name="Scott" />
2
<Students RollNo="250" Name="Tim" />

1. Complex XQuery Example:

DECLARE @xlHollywood AS XML =
'<Hollywood ntYear="2013">
  <Movie vcMovieName="PAIN &amp; GAIN" vcRunTime="2 hr 9 mins" dtRelease="2013-04-26T00:00:00">
    <Actor vcActorName="Mark Wahlberg" />
  </Movie>
  <Movie vcMovieName="OBLIVION" vcRunTime="2 hr 5 mins" dtRelease="2013-04-19T00:00:00">
    <Actor vcActorName="Tom Cruise" />
  </Movie>
  <Movie vcMovieName="IRON MAN 3" vcRunTime="2 hr 10 mins" dtRelease="2013-05-03T00:00:00">
    <Actor vcActorName="Robert Downey" />
  </Movie>
  <Movie vcMovieName="EVIL DEAD" vcRunTime="1 hr 31 mins" dtRelease="2013-04-05T00:00:00">
    <Actor vcActorName="Jane Levy" />
  </Movie>
  <Movie vcMovieName="MAN OF STEEL" vcRunTime="2 hr 23 mins" dtRelease="2013-06-14T00:00:00">
    <Actor vcActorName="Henry Cavill" />
    <Actor vcActorName="Amy Adams" />
    <Actor vcActorName="Diane Lane" />
  </Movie>
</Hollywood>'

DECLARE @dtShowTime AS TIME = '18:30'
DECLARE @ntCounter AS INT = 0

SELECT
      T.C.value('lower-case(@vcMovieName)','VARCHAR(100)') AS vcMovieName,
      T.C.value('@vcRunTime','VARCHAR(15)') AS vcRunTime,
      T.C.value('@dtRelease','DATE') AS dtRelease,
      T.C.value('(Actor/@vcActorName)[1]','VARCHAR(100)') AS vcMainActorName,
     
T.C.query('data(for $a in Actor  return concat(",",$a/@vcActorName))').value('substring(.,2)','VARCHAR(MAX)')  AS vcAllActorsName,

      T.C.query('Actor') AS vcActorsXml,
      T.C.value('count(Actor)','INT') AS ntTolalActors,
      T.C.value('../@ntYear','INT') AS [Year],

      T.C.query('data(for $l in ../Movie order by ($l/@dtRelease)[1] descending return ($l/@vcMovieName))[1]').value('.','VARCHAR(100)')
vcLatestMovie,

      T.C.query('data(for $l in ../Movie order by ($l/@dtRelease)[1] descending return  concat(",",$l/@vcMovieName))').value('substring(.,2)','VARCHAR(Max)') vcMovieListSortedByReleaseDate,

      T.C.query('sql:variable("@dtShowTime")') AS vcLocalVariable
FROM @xlHollywood.nodes('/Hollywood/Movie') T(C)

Sample output:

vcMovieName
vcRunTime
dtRelease
vcMainActorName
vcAllActorsName
vcActorsXml
pain & gain
2 hr 9 mins
2013-04-26
Mark Wahlberg
Mark Wahlberg
<Actor vcActorName="Mark Wahlberg" />
oblivion
2 hr 5 mins
2013-04-19
Tom Cruise
Tom Cruise
<Actor vcActorName="Tom Cruise" />
iron man 3
2 hr 10 mins
2013-05-03
Robert Downey
Robert Downey
<Actor vcActorName="Robert Downey" />
evil dead
1 hr 31 mins
2013-04-05
Jane Levy
Jane Levy
<Actor vcActorName="Jane Levy" />
man of steel
2 hr 23 mins
2013-06-14
Henry Cavill
Henry Cavill ,Amy Adams ,Diane Lane
<Actor vcActorName="Henry Cavill" /><Actor vcActorName="Amy Adams" /><Actor vcActorName="Diane Lane" />

ntTolalActors
Year
vcLatestMovie
vcMovieListSortedByReleaseDate
vcLocalVariable
1
2013
MAN OF STEEL
MAN OF STEEL ,IRON MAN 3 ,PAIN & GAIN ,OBLIVION ,EVIL DEAD
18:30:00
1
2013
MAN OF STEEL
MAN OF STEEL ,IRON MAN 3 ,PAIN & GAIN ,OBLIVION ,EVIL DEAD
18:30:00
1
2013
MAN OF STEEL
MAN OF STEEL ,IRON MAN 3 ,PAIN & GAIN ,OBLIVION ,EVIL DEAD
18:30:00
1
2013
MAN OF STEEL
MAN OF STEEL ,IRON MAN 3 ,PAIN & GAIN ,OBLIVION ,EVIL DEAD
18:30:00
3
2013
MAN OF STEEL
MAN OF STEEL ,IRON MAN 3 ,PAIN & GAIN ,OBLIVION ,EVIL DEAD
18:30:00

2. Complex XQuery Example:

SELECT

      T.C.value('lower-case(../@vcMovieName)','VARCHAR(100)') AS vcMovieName,
      T.C.value('../@vcRunTime','VARCHAR(15)') AS vcRunTime,
      T.C.value('../@dtRelease','DATE') AS dtRelease,
      T.C.value('(@vcActorName)[1]','VARCHAR(100)') AS vcActorName,
      T.C.query('data(for $a in ../Actor  return concat(",",$a/@vcActorName))').value('substring(.,2)','VARCHAR(MAX)')  AS vcAllActorsName,
      T.C.query('.') AS vcActorsXml,
      T.C.value('count(../Actor)','INT') AS ntTolalActors,
      T.C.value('../../@ntYear','INT') AS [Year],
      T.C.query('data(for $l in ../../Movie order by ($l/@dtRelease)[1] descending return ($l/@vcMovieName))[1]').value('.','VARCHAR(100)') vcLatestMovie,
      T.C.query('data(for $l in ../../Movie order by ($l/@dtRelease)[1] descending return  concat(",",$l/@vcMovieName))').value('substring(.,2)','VARCHAR(Max)') vcMovieListSortedByReleaseDate
FROM @xlHollywood.nodes('/Hollywood/Movie/Actor') T(C)

Sample output:

vcMovieName
vcRunTime
dtRelease
vcActorName
vcAllActorsName
vcActorsXml
pain & gain
2 hr 9 mins
2013-04-26
Mark Wahlberg
Mark Wahlberg
<Actor vcActorName="Mark Wahlberg" />
oblivion
2 hr 5 mins
2013-04-19
Tom Cruise
Tom Cruise
<Actor vcActorName="Tom Cruise" />
iron man 3
2 hr 10 mins
2013-05-03
Robert Downey
Robert Downey
<Actor vcActorName="Robert Downey" />
evil dead
1 hr 31 mins
2013-04-05
Jane Levy
Jane Levy
<Actor vcActorName="Jane Levy" />
man of steel
2 hr 23 mins
2013-06-14
Henry Cavill
Henry Cavill ,Amy Adams ,Diane Lane
<Actor vcActorName="Henry Cavill" />
man of steel
2 hr 23 mins
2013-06-14
Amy Adams
Henry Cavill ,Amy Adams ,Diane Lane
<Actor vcActorName="Amy Adams" />
man of steel
2 hr 23 mins
2013-06-14
Diane Lane
Henry Cavill ,Amy Adams ,Diane Lane
<Actor vcActorName="Diane Lane" />

ntTolalActors
Year
vcLatestMovie
vcMovieListSortedByReleaseDate
1
2013
MAN OF STEEL
MAN OF STEEL ,IRON MAN 3 ,PAIN & GAIN ,OBLIVION ,EVIL DEAD
1
2013
MAN OF STEEL
MAN OF STEEL ,IRON MAN 3 ,PAIN & GAIN ,OBLIVION ,EVIL DEAD
1
2013
MAN OF STEEL
MAN OF STEEL ,IRON MAN 3 ,PAIN & GAIN ,OBLIVION ,EVIL DEAD
1
2013
MAN OF STEEL
MAN OF STEEL ,IRON MAN 3 ,PAIN & GAIN ,OBLIVION ,EVIL DEAD
3
2013
MAN OF STEEL
MAN OF STEEL ,IRON MAN 3 ,PAIN & GAIN ,OBLIVION ,EVIL DEAD
3
2013
MAN OF STEEL
MAN OF STEEL ,IRON MAN 3 ,PAIN & GAIN ,OBLIVION ,EVIL DEAD
3
2013
MAN OF STEEL
MAN OF STEEL ,IRON MAN 3 ,PAIN & GAIN ,OBLIVION ,EVIL DEAD

1 comment:

  1. Anonymous1/24/2017

    Excellent tutorial!!

    ReplyDelete