T-SQL如何提取示例数据库中中的XML内容

2011-10-23 04:46:33 313 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

君正T31x metartc集成测试 metartc为国内杨大侠部分开源的一款用于嵌入式领域webrtc库，它与谷歌官方webrtc相比，编译到君正平台的可执行文件仅有5M即可实现webrtc的通信。1. 目前实现的是基于开源版v5.0-b0版本做的简单修改，官方的好几个版本都多少缺点东西，我这里给整理了一下。目前是局域网连接的webrtc，后续尝试走stun和turn试一下NAT穿越的效果。阅读详情

如题,我想提取AdventureWorks示例数据库表Individual下的Demographics列字段中的各个节点值，我先获取表内一条数据
放入XML变量中进行测试；如下：



USE AdventureWorks

GO



DECLARE @TEST XML

SET @TEST=(SELECT TOP 1 Demographics FROM Sales.Individual)

PRINT CONVERT(NVARCHAR(MAX),@TEST)

得到的XML文件内容如下:



<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">

<TotalPurchaseYTD>8248.99</TotalPurchaseYTD>

<DateFirstPurchase>2001-07-22Z</DateFirstPurchase>

<BirthDate>1966-04-08Z</BirthDate>

<MaritalStatus>M</MaritalStatus>

<YearlyIncome>75001-100000</YearlyIncome>

<Gender>M</Gender>

<TotalChildren>2</TotalChildren>

<NumberChildrenAtHome>0</NumberChildrenAtHome>

<Education>Bachelors </Education>

<Occupation>Professional</Occupation>

<HomeOwnerFlag>1</HomeOwnerFlag>

<NumberCarsOwned>0</NumberCarsOwned>

<CommuteDistance>1-2 Miles</CommuteDistance>

</IndividualSurvey>

----
我如下提取为何提取到的都是空值:



DECLARE @TEST XML

SET @TEST=(SELECT TOP 1 Demographics FROM Sales.Individual)

PRINT CONVERT(NVARCHAR(MAX),@TEST)

--SELECT TotalPurchaseYTD=T.C.value('/TotalPurchaseYTD.[1]','nvarchar(20)')

--FROM @TEST.nodes('/IndividualSurvey') T(C)



SELECT TotalPurchaseYTD =

       @TEST.value('(/IndividualSurvey/TotalPurchaseYTD)[1]', 'nvarchar(20)'),

       BirthDate=@TEST.value('(/IndividualSurvey/BirthDate)[1]', 'DATETIME'),

       MaritalStatus=@TEST.value('(/IndividualSurvey/MaritalStatus)[1]', 'nvarchar(2)')



--取得的都是空值

如上取得的都是空值，是我写法错了？还是XML执行了XML架构命名空间等的原因?
刚刚学习XML数据类型的操作,了解很少,求帮助。

--帮助文档内的方法都是可行的呀，如下：



DECLARE @myDoc xml

SET @myDoc = '<Root>

<ProductDescription ProductID="1" ProductName="Road Bike">

<Features>

  <Warranty>1 year parts and labor</Warranty>

  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>

</Features>

</ProductDescription>

</Root>'



SELECT  ProdID =  @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' ),

        ProductName=@myDoc.value('(/Root/ProductDescription/@ProductName)[1]', 'nvarchar(20)' ),

        XX=@myDoc.value('(/Root/ProductDescription/Features/Warranty)[1]', 'nvarchar(20)' )



--结果如下:

ProdID      ProductName          XX

----------- -------------------- --------------------

1           Road Bike            1 year parts and lab



(1 行受影响)