Suggest you declare the XMLNAMESPACES with a default namespace, makes the query more readable and less error prone.
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @MYTAB TABLE (
jobid int not null,
info ntext null
)
INSERT INTO @MYTAB VALUES
(1, '<?xml version="1.0"?><enterprise xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://Czm.Connection.Lms/Common/LmsGeneric.xsd"><OtherCVueAttributes><SyStudentId>0</SyStudentId><LMSVendorId>4</LMSVendorId><CampusId>7</CampusId><TermId>11272</TermId><DeliveryMethodId>3</DeliveryMethodId><ParentTermID>-1</ParentTermID><CourseId>34269</CourseId><ShiftId>-1</ShiftId><SchoolStatusId>0</SchoolStatusId><CourseSectionId>75489</CourseSectionId></OtherCVueAttributes><OtherLMSAttributes><Action /><MaxStudent>0</MaxStudent><CourseCode /><ShortName /><LongName /></OtherLMSAttributes><OtherMCUAttributes /><ProcessingStatus><Result>OK</Result></ProcessingStatus><Organization /><Category /><comments /><properties><comments /><datasource>MYDATASOURCE</datasource><datetime>2018-09-13T14:57:41</datetime><extension /><target>CANVAS</target></properties><person><comments /><sourcedid><source>MYDATASOURCE_STAFF</source><id>10896</id></sourcedid><userid /><name><fn>Clinical Instructor</fn><n><family>Clinical Instructor</family><given>UTA</given><prefix></prefix><partname partnametype="Middlename"></partname></n></name><demographics /><email /><adr><extadd></extadd><locality /><region></region><pcode></pcode><street /></adr><photo /><institutionrole institutionroletype="Student" primaryrole="Yes" /><extension /><InstitutionRoleTypeString>Student</InstitutionRoleTypeString><systemrole>1</systemrole><tel></tel></person><group><GroupExtension><Semester /><Active>0</Active></GroupExtension><comments>-1</comments><sourcedid><source>MYDATASOURCE</source><id>75489</id></sourcedid><grouptype><typevalue>Course</typevalue></grouptype><description><Title>Introduction to Biology</Title><Description>Introduction to Biology</Description><Code>TOH505</Code><Section /></description><org><orgunit>TOH505</orgunit></org><timeframe><begin>8/10/2018 12:00:00 AM</begin><end>12/18/2018 12:00:00 AM</end></timeframe><enrollcontrol /><relationship><sourcedid><source>MYDATASOURCE</source><id>75489</id></sourcedid><label>Enrollable Node</label></relationship><extension /></group><membership><comments /><sourcedid /><member><comments /><sourcedid /><role><Extension><EnrollSchedID>0</EnrollSchedID><EnrollSchedStatusChangesExtractId>0</EnrollSchedStatusChangesExtractId></Extension><userid /><comments /><timeframe><begin /><end /></timeframe><RelationType>Student</RelationType><Action><ActionType>Undefined</ActionType></Action></role></member></membership></enterprise>');
;WITH XMLNAMESPACES ( DEFAULT 'http://Czm.Connection.Lms/Common/LmsGeneric.xsd')
,BASE_DATA AS
(
SELECT
M.jobid
,CONVERT(XML,M.info,1) AS XMLINFO
FROM @MYTAB M
)
SELECT
BD.jobid
,BASE.DATA.value('(OtherCVueAttributes/SyStudentId/text())[1]' ,'INT') AS SyStudentId
,BASE.DATA.value('(OtherCVueAttributes/LMSVendorId/text())[1]' ,'INT') AS LMSVendorId
,BASE.DATA.value('(OtherCVueAttributes/CampusId/text())[1]' ,'INT') AS CampusId
,BASE.DATA.value('(OtherCVueAttributes/TermId/text())[1]' ,'INT') AS TermId
,BASE.DATA.value('(OtherCVueAttributes/DeliveryMethodId/text())[1]' ,'INT') AS DeliveryMethodId
,BASE.DATA.value('(OtherCVueAttributes/ParentTermID/text())[1]' ,'INT') AS ParentTermID
,BASE.DATA.value('(OtherCVueAttributes/CourseId/text())[1]' ,'INT') AS CourseId
,BASE.DATA.value('(OtherCVueAttributes/ShiftId/text())[1]' ,'INT') AS ShiftId
,BASE.DATA.value('(OtherCVueAttributes/SchoolStatusId/text())[1]' ,'INT') AS SchoolStatusId
,BASE.DATA.value('(OtherCVueAttributes/CourseSectionId/text())[1]' ,'INT') AS CourseSectionId
FROM BASE_DATA BD
CROSS APPLY BD.XMLINFO.nodes('enterprise') BASE(DATA);
Thanks to everyone for these very helpful suggestions and tips. Querying XML has always been a bit baffling to me. I really appreciate it!
Eirikur Eiriksson - Thursday, September 20, 2018 12:15 AM
One, you know that beginninator before the WITH really annoys me, especially since the preceding statement is properly terminated with a semicolon and the statement with the WITH is also properly terminated witha semicolon. But that is just my pet peeve.
What I would really like is a bit of an explanation about how the default namespace works in this instance. Like the OP, I am still confused when it comes to working with XML and I am trying to learn more.
Eirikur Eiriksson - Thursday, September 20, 2018 12:15 AM
Thanks Eirikur.
I have never seen the DEFAULT namespace before.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply