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