XML查询示例二

来源：互联网发布：做网络销售需要会什么编辑：程序博客网时间：2024/06/15 02:30

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 NumberCarsOwnedFROM Person.PersonWHERE BusinessEntityID = 10000GO

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 NumberCarsOwnedFROM Person.PersonCROSS APPLY Demographics.nodes('/ns:IndividualSurvey') AS T(C)WHERE BusinessEntityID = 10000GO

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