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 & 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
|
Excellent tutorial!!
ReplyDelete