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 (