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

[怪众生太美丽]

如题,我想提取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 行受影响)

[-晴天]

这样就有了:

DECLARE @TEST XML
SET @TEST = '<IndividualSurvey>
<TotalPurchaseYTD>8248.99</TotalPurchaseYTD>
</IndividualSurvey>
'
SELECT TotalPurchaseYTD =
       @TEST.value('(/IndividualSurvey/TotalPurchaseYTD)[1]','decimal(18,2)') /*'nvarchar(20)')
/*
TotalPurchaseYTD
---------------------------------------
8248.99

(1 行受影响)

*/

[怪众生太美丽]

谢谢晴天兄的回答,但是不是这个问题,看帮助文档解决了问题,是因为少了命名空间
如下就好了：

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('
    declare namespace dd="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";       
    (/dd:IndividualSurvey/dd:TotalPurchaseYTD)[1]','nvarchar(20)') 
/*
--------------------
8248.99

(1 行受影响)

[Quote=引用 1 楼 qianjin036a 的回复:]
这样就有了:

SQL code

DECLARE @TEST XML
SET @TEST = '<IndividualSurvey>
<TotalPurchaseYTD>8248.99</TotalPurchaseYTD>
</IndividualSurvey>
'
SELECT TotalPurchaseYTD =
@TEST.value('(/Individ……
[/Quote]

[叶子]

不是数据类型的问题

[叶子]

XML Namespaces

[--小F--]

[Quote=引用 2 楼 guoweifyj 的回复:]
谢谢晴天兄的回答,但是不是这个问题,看帮助文档解决了问题,是因为少了命名空间
如下就好了：

SQL code

DECLARE @TEST XML
SET @TEST=(SELECT TOP 1 Demographics FROM Sales.Individual)
PRINT CONVERT(NVARCHAR(MAX),@TEST)
--SELECT TotalPurchase……
[/Quote]