XML查询示例四

分离XML

DECLARE @xml XML

SET @xml = (
       SELECT  TOP 2 Name, 
           BusinessEntityID AS ID, 
           Demographics.query('/')
       FROM Sales.Store AS Store
       WHERE SalesPersonID = 282
       FOR XML AUTO, ROOT('StoreSurveys')
       )

SELECT @xml 
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey' AS ns)
SELECT C.value('../@ID','int') AS BusinessEntityID,
   C.value('../@Name','varchar(50)') AS StoreName,
   C.query('.') AS Demographics
FROM @xml.nodes('/StoreSurveys/Store/ns:StoreSurvey') AS T(C)
GO

BusinessEntityID StoreName Demographics
312 Vinyl and Plastic Goods Corporation <StoreSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"><AnnualSales>1500000</AnnualSales><AnnualRevenue>150000</AnnualRevenue><BankName>Primary Bank &amp; Reserve</BankName><BusinessType>OS</BusinessType><YearOpened>1980</YearOpened><Specialty>Mountain</Specialty><SquareFeet>41000</SquareFeet><Brands>4+</Brands><Internet>DSL</Internet><NumberEmployees>43</NumberEmployees></StoreSurvey>
324 Valley Toy Store <StoreSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"><AnnualSales>300000</AnnualSales><AnnualRevenue>30000</AnnualRevenue><BankName>Reserve Security</BankName><BusinessType>BM</BusinessType><YearOpened>1979</YearOpened><Specialty>Mountain</Specialty><SquareFeet>9000</SquareFeet><Brands>2</Brands><Internet>T1</Internet><NumberEmployees>6</NumberEmployees></StoreSurvey>

 

或

DECLARE @xml XML

SET @xml = (
       SELECT  TOP 2 Name, 
           BusinessEntityID AS ID, 
           Demographics.query('/')
       FROM Sales.Store AS Store
       WHERE SalesPersonID = 282
       FOR XML AUTO, ROOT('StoreSurveys')
       )

SELECT @xml 
SELECT C.value('@ID','int') AS BusinessEntityID,
   C.value('@Name','varchar(50)') AS StoreName,
   C.query('./child::node()') AS Demographics
FROM @xml.nodes('/StoreSurveys/Store') AS T(C)
GO