Can any one tell me what will be the query if I store this xml into database table (say “Pupiltable”) xml column (say “pupilcolumn”)? I want to set @x value from database table. I use following code for set @x value.But it dose not work.
DECLARE @x XML SELECT @x= pupilcolumn from Pupiltable SELECT Pupil.value('PupilID[1]' ,'varchar(15)') as UPN, Pupil.value('Surname[1]' ,'varchar(10)') as Surname, Pupil.value('Forename[1]' ,'varchar(10)') as Forename, att.value('SessionsPossible[1]' ,'int') as SessionsPossible, det.value('AttendanceReason[1]' ,'varchar(5)') as AttendanceReason, det.value('AbsenceSessions[1]' ,'varchar(5)') as AttendanceSessions FROM @x.nodes('//Pupils/Pupil/PupilIdentifiers') Pupils(Pupil) OUTER APPLY Pupil.nodes('../Attendance/TermlyAttendance') Term(att) OUTER APPLY att.nodes('SessionDetails/SessionDetail') Sess(det)
Posts
Can any one tell me what will be the query if I store this xml into database table (say “Pupiltable”) xml column (say “pupilcolumn”)?
ReplyDeleteI want to set @x value from database table.
I use following code for set @x value.But it dose not work.
DECLARE @x XML
SELECT @x= pupilcolumn from Pupiltable
SELECT
Pupil.value('PupilID[1]' ,'varchar(15)') as UPN,
Pupil.value('Surname[1]' ,'varchar(10)') as Surname,
Pupil.value('Forename[1]' ,'varchar(10)') as Forename,
att.value('SessionsPossible[1]' ,'int') as SessionsPossible,
det.value('AttendanceReason[1]' ,'varchar(5)') as AttendanceReason,
det.value('AbsenceSessions[1]' ,'varchar(5)') as AttendanceSessions
FROM
@x.nodes('//Pupils/Pupil/PupilIdentifiers') Pupils(Pupil)
OUTER APPLY Pupil.nodes('../Attendance/TermlyAttendance') Term(att)
OUTER APPLY att.nodes('SessionDetails/SessionDetail') Sess(det)
thanks
Here is an example that shows how to load the value of an XML column to a variable.
ReplyDeleteI hope this will help you.
-- create a table with an XML column
DECLARE @t TABLE (data XML)
-- insert a row with an XML value
INSERT INTO @t SELECT (
SELECT
'test' AS val
FOR XML PATH(''), ROOT('data')
)
-- DECLARE an XML variable
DECLARE @x XML
-- load the XML value to the variable
SELECT
@x = data
FROM @t
-- read values from the XML variable
SELECT @x.value('(data/val)[1]', 'VARCHAR(20)') AS val
/*
val
--------------------
test
*/