Converting XML in ntext field to columns/rows

Question

Converting XML in ntext field to columns/rows

DataAnalyst011

SSCarpal Tunnel

Points: 4035
More actions
September 19, 2018 at 11:15 am

#372884

Hi All,
I've been working on this a few days and I'm stuck. I have field (I can't change) that is ntext containing XML. Basically, I need to pull all the values out and store them in a normal table format.
Here is some sample data. The actual table contains around 1000 rows. But it was very hard to create even one row of sample data! Let me know if more rows would be helpful, but the XML structure is the same. Any help is so appreciated!
CREATE TABLE mytable ( jobid int not null, info ntext null )
INSERT INTO mytable 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>')
Shredding xml is case-sensitive
/ is the separator between the elements
xxx: indicates a specific namespace. I have seen <ns1: elementName @AttribName="Attribute Value">Element Value</elementName>
*: indicates "ANY" namespace

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

DataAnalyst011 - Wednesday, September 19, 2018 11:15 AM
Hi All,
I've been working on this a few days and I'm stuck. I have field (I can't change) that is ntext containing XML. Basically, I need to pull all the values out and store them in a normal table format.
Here is some sample data. The actual table contains around 1000 rows. But it was very hard to create even one row of sample data! Let me know if more rows would be helpful, but the XML structure is the same. Any help is so appreciated!
CREATE TABLE mytable ( jobid int not null, info ntext null )
INSERT INTO mytable 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>')

Not sure what you have tried as far as shredding the XML (and unfortunately that isn't my strong suit) but have you tried creating a view over the table and casting the info column as XML and using the view to process the data?

sgmunson SSC Guru Points: 110640 More actions · Answer 2

DataAnalyst011 - Wednesday, September 19, 2018 11:15 AM
Hi All,
I've been working on this a few days and I'm stuck. I have field (I can't change) that is ntext containing XML. Basically, I need to pull all the values out and store them in a normal table format.
Here is some sample data. The actual table contains around 1000 rows. But it was very hard to create even one row of sample data! Let me know if more rows would be helpful, but the XML structure is the same. Any help is so appreciated!
CREATE TABLE mytable ( jobid int not null, info ntext null )
INSERT INTO mytable 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>')

The reason that using a view to CAST the ntext column into xml is a good idea is because you can't do any kind of string manipulation on an NTEXT or TEXT data type. It just isn't allowed. The only viable operation on such columns is CAST or CONVERT, and once you've done that, you can then manipulate the data. This is one of several reasons that NTEXT, TEXT, and IMAGE data types have been deprecated.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

DesNorton SSC-Insane Points: 24401 More actions · Answer 3

This should get you started ...
WITH cteSourceData AS ( SELECT jobid , info = CAST(info AS xml) FROM mytable --WHERE jobid = 1 ) SELECT src.jobid , SyStudentId = n.d.value('(/*:enterprise/*:OtherCVueAttributes/*:SyStudentId/text())[1]', 'int') , LMSVendorId = n.d.value('(/*:enterprise/*:OtherCVueAttributes/*:LMSVendorId/text())[1]', 'int') , CampusId = n.d.value('(/*:enterprise/*:OtherCVueAttributes/*:CampusId/text())[1]', 'int') , TermId = n.d.value('(/*:enterprise/*:OtherCVueAttributes/*:TermId/text())[1]', 'int') , DeliveryMethodId = n.d.value('(/*:enterprise/*:OtherCVueAttributes/*:DeliveryMethodId/text())[1]', 'int') , ParentTermID = n.d.value('(/*:enterprise/*:OtherCVueAttributes/*:ParentTermID/text())[1]', 'int') , CourseId = n.d.value('(/*:enterprise/*:OtherCVueAttributes/*:CourseId/text())[1]', 'int') , ShiftId = n.d.value('(/*:enterprise/*:OtherCVueAttributes/*:ShiftId/text())[1]', 'int') , SchoolStatusId = n.d.value('(/*:enterprise/*:OtherCVueAttributes/*:SchoolStatusId/text())[1]', 'int') , CourseSectionId = n.d.value('(/*:enterprise/*:OtherCVueAttributes/*:CourseSectionId/text())[1]', 'int') , [Action] = n.d.value('(/*:enterprise/*:OtherLMSAttributes/*:Action/text())[1]', 'int') , MaxStudent = n.d.value('(/*:enterprise/*:OtherLMSAttributes/*:MaxStudent/text())[1]', 'int') , CourseCode = n.d.value('(/*:enterprise/*:OtherLMSAttributes/*:CourseCode/text())[1]', 'int') , ShortName = n.d.value('(/*:enterprise/*:OtherLMSAttributes/*:ShortName/text())[1]', 'int') , LongName = n.d.value('(/*:enterprise/*:OtherLMSAttributes/*:LongName/text())[1]', 'int') , OtherMCUAttributes = n.d.value('(/*:enterprise/*:OtherMCUAttributes/text())[1]', 'varchar(100)') , ProcessingResult = n.d.value('(/*:enterprise/*:ProcessingStatus/*:Result/text())[1]', 'varchar(100)') , Organization = n.d.value('(/*:enterprise/*:Organization/text())[1]', 'varchar(100)') , Category = n.d.value('(/*:enterprise/*:Category/text())[1]', 'varchar(100)') , comments = n.d.value('(/*:enterprise/*:comments/text())[1]', 'varchar(100)') FROM cteSourceData AS src OUTER APPLY src.info.nodes('.') AS n(d)

How to post data/code on a forum to get the best help.

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 4

1) Thanks a TON! This is so helpful.

2) I had a couple of lines that were failing on syntax. After looking closer I found stuff like this:
, DeliveryMethodId = n.d.value('(/*:enterprise/*:OtherCVueAttributes/*BigGrineliveryMethodId/text())[1]', 'int')
(note the "BigGrin" insert from auto-emoticon update. I got a kick out of that)

3) I tried something like this but couldn't get it to work. Would you mind explaining a little how this works? For instance, why do you have to use the *: before the XML levels? (e.g. '(/*:enterprise/*:OtherCVueAttributes.... )

DesNorton SSC-Insane Points: 24401 More actions · Answer 5

DataAnalyst011 - Wednesday, September 19, 2018 3:00 PM
1) Thanks a TON! This is so helpful.
2) I had a couple of lines that were failing on syntax. After looking closer I found stuff like this:
, DeliveryMethodId = n.d.value('(/*:enterprise/*:OtherCVueAttributes/*BigGrineliveryMethodId/text())[1]', 'int')
(note the "BigGrin" insert from auto-emoticon update. I got a kick out of that)
3) I tried something like this but couldn't get it to work. Would you mind explaining a little how this works? For instance, why do you have to use the *: before the XML levels? (e.g. '(/*:enterprise/*:OtherCVueAttributes.... )

I am not very strong on XML.
That said, my inderstanding is that ...

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 6

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);

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 7

Thanks to everyone for these very helpful suggestions and tips. Querying XML has always been a bit baffling to me. I really appreciate it!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Eirikur Eiriksson - Thursday, September 20, 2018 12:15 AM
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);

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.

DesNorton SSC-Insane Points: 24401 More actions · Answer 9

Eirikur Eiriksson - Thursday, September 20, 2018 12:15 AM
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 Eirikur.
I have never seen the DEFAULT namespace before.

How to post data/code on a forum to get the best help.