如何使用SQL Server OPENROWSET和CROSS APPLY获取XML属性

你的问题出在XPath路径导航错误：当前x1.Applicant对应的是Root/Applicants/Applicant节点，要获取父节点Applicants的BuildingCode属性，只需要用../@BuildingCode即可，你用了../../@BuildingCode，这会定位到Root节点（Applicant的父节点是Applicants，父父节点是Root），而Root并没有BuildingCode属性，所以读取失败。

修正后的SQL代码 #

SELECT 
   x1.Applicant.value('../@BuildingCode', 'VARCHAR(15)') AS BuildingCode,
   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);

更清晰的写法（推荐） #

可以先定位到Applicants节点，再关联其子节点Applicant，这样无需向上导航，代码可读性更强：

SELECT 
   x2.Applicants.value('@BuildingCode', 'VARCHAR(15)') AS BuildingCode,
   x2.Applicants.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') AS x2(Applicants)
CROSS APPLY x2.Applicants.nodes('Applicant') AS x1(Applicant);

另外注意你的XML中CourseName元素的内容有多余的双引号（Data Analytics 101 - Beginner"），如果是输入错误建议修正，避免后续读取出现异常。

内容的提问来源于stack exchange，提问作者Robertcode