How to use SQL Server openrowset and Cross Apply to get an XML Attribute-CodePudding

How do I get the value of an XML Attribute from XML that has been read from a file in SQL Server? I am able to read values from XML elements but not the attribute for an XML element. Below is the XML I am using. I would like to read the BuildingCode attribute from the Applicants element.

XML in an external file:

<?xml version="1.0" encoding="utf-8"?>
<Root>
  <Applicants BuildingCode="7578">
    <SubCode>2000</SubCode>
      <Applicant>
        <CourseName>Data Analytics 101 - Beginner"</CourseName>
        <CourseCode>DA101</CourseCode>
        <StartDate>2022-08-01 08:00:00</StartDate>
        <FirstName>Joe</FirstName>
        <LastName>Stewart</LastName>
        <StudentID>301</StudentID>
        <Membership>Gold</Membership>
      </Applicant>
      <Applicant>
        <CourseName>Data Analytics 101 - Beginner"</CourseName>
        <CourseCode>DA101</CourseCode>
        <StartDate>2022-08-01 08:00:00</StartDate>
        <FirstName>Sandy</FirstName>
        <LastName>Gomez</LastName>
        <StudentID>302</StudentID>
        <Membership>Bronze</Membership>
      </Applicant>
  </Applicants>
</Root>

SQL to read the XML File and display the values in rows:

SELECT 
   x1.Applicant.value('(../../@BuildingCode/text())[1]', 'VARCHAR(15)'),
   x1.Applicant.value('(../SubCode/text())[1]', 'VARCHAR(15)') as SubCode,
   x1.Applicant.value('(CourseName/text())[1]', 'VARCHAR(50)') as CourseName,
   x1.Applicant.value('(CourseCode/text())[1]', 'VARCHAR(20)') as CourseCode,
   x1.Applicant.value('(StartDate/text())[1]', 'datetime') as StartDate,
   x1.Applicant.value('(FirstName/text())[1]', 'VARCHAR(50)') as FirstName,
   x1.Applicant.value('(LastName/text())[1]', 'VARCHAR(50)') as LastName,
   x1.Applicant.value('(StudentID/text())[1]', 'int') as StudentID,
   x1.Applicant.value('(Membership/text())[1]', 'VARCHAR(20)') as Membership

FROM OPENROWSET(BULK 'C:\FilesForTesting\XmlLoadTest101.xml', SINGLE_BLOB) AS T1(BinaryData)
CROSS APPLY (VALUES (
    CAST(T1.BinaryData AS xml)
) ) AS T2(XMLFromFile)

CROSS APPLY T2.XMLFromFile.nodes('Root/Applicants/Applicant') AS x1(Applicant);

The SQL works if I comment out the first select column which tries to read the BuildingCode attribute from the Applicants element. I tried to navigate back up from the /Applicant element to get it but my attempt does not work. I am using an @ symbol in front of the BuildingCode because I saw other internet samples doing this when trying to get the attribute of an element but again it does not work for me in this example.

CodePudding user response：

You need to first descend to the Applicants node, then feed that into another .nodes call to get the Applicant nodes

SELECT
   x1.Applicants.value('@BuildingCode', 'VARCHAR(15)'),
   x1.Applicants.value('(SubCode/text())[1]', 'VARCHAR(15)') as SubCode,
   x2.Applicant.value('(CourseName/text())[1]', 'VARCHAR(50)') as CourseName,
   x2.Applicant.value('(CourseCode/text())[1]', 'VARCHAR(20)') as CourseCode,
   x2.Applicant.value('(StartDate/text())[1]', 'datetime') as StartDate,
   x2.Applicant.value('(FirstName/text())[1]', 'VARCHAR(50)') as FirstName,
   x2.Applicant.value('(LastName/text())[1]', 'VARCHAR(50)') as LastName,
   x2.Applicant.value('(StudentID/text())[1]', 'int') as StudentID,
   x2.Applicant.value('(Membership/text())[1]', 'VARCHAR(20)') as Membership

FROM OPENROWSET(BULK 'C:\FilesForTesting\XmlLoadTest101.xml', SINGLE_BLOB) AS T1(BinaryData)
CROSS APPLY (VALUES (
    CAST(T1.BinaryData AS xml)
) ) AS T2(XMLFromFile)

CROSS APPLY T2.XMLFromFile.nodes('Root/Applicants') AS x1(Applicants)
CROSS APPLY x1.Applicants.nodes('Applicant') AS x2(Applicant);

db<>fiddle