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