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