XML数据提取详解：VALUE与NODES方法-优快云博客

本文链接：https://blog.youkuaiyun.com/sfrankzhang/article/details/8151171

本文介绍了如何从XML列中提取数据，分别展示了使用VALUE和NODES两个方法的具体示例，如提取包括 FirstName、MiddleName、LastName 等个人信息，以及 Occupation、Education、HomeOwnerFlag 和 NumberCarsOwned 等生活状态信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1，提取XML列，使用VALUE

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey' AS ns)
SELECT FirstName, 
   MiddleName,
   LastName,
   Demographics.value('(/ns:IndividualSurvey/ns:Occupation)[1]','varchar(50)') AS Occupation,
   Demographics.value('(/ns:IndividualSurvey/ns:Education)[1]','varchar(50)') AS Education,
   Demographics.value('(/ns:IndividualSurvey/ns:HomeOwnerFlag)[1]','bit') AS HomeOwnerFlag,
   Demographics.value('(/ns:IndividualSurvey/ns:NumberCarsOwned)[1]','int') AS NumberCarsOwned
FROM Person.Person
WHERE BusinessEntityID = 10000
GO

FirstName MiddleName LastName Occupation Education HomeOwnerFlag NumberCarsOwned
Haley C Mitchell Management Graduate Degree 0 4

2，提取XML列，使用NODES

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey' AS ns)
SELECT FirstName, 
   MiddleName,
   LastName,
   C.value('ns:Occupation[1]','varchar(50)') AS Occupation,
   C.value('ns:Education[1]','varchar(50)') AS Education,
   C.value('ns:HomeOwnerFlag[1]','bit') AS HomeOwnerFlag,
   C.value('ns:NumberCarsOwned[1]','int') AS NumberCarsOwned
FROM Person.Person
CROSS APPLY Demographics.nodes('/ns:IndividualSurvey') AS T(C)
WHERE BusinessEntityID = 10000
GO

FirstName MiddleName LastName Occupation Education HomeOwnerFlag NumberCarsOwned
Haley C Mitchell Management Graduate Degree 0 4