如何从包含xml命名空间的XML列获取数据（SQL Server 2005）

I google a lot and got no luck. I can't retrieve data from XML column which data came from web service using sp_OAGetProperty.

我谷歌很多,没有运气。我无法使用sp_OAGetProperty从XML列检索哪些数据来自Web服务。

the XML Column contain..

XML列包含..

<ArrayOfCustomerInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/">
   <Customer CustCode="001">
      <CustName>John</CustName>
      <Queues>
         <Q>
            <No>10</No>
            <Line>1</Line>
         </Q>
      </Queues>
   </Customer> 
</ArrayOfCustomerInfo>

I got NULL when I execute following statement

我执行以下语句时得到NULL

(but works fine if I remove all XML namespace xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/")

(但如果删除所有XML命名空间xmlns,则工作正常:xsi =“http://www.w3.org/2001/XMLSchema-instance”xmlns:xsd =“http://www.w3.org/2001/XMLSchema”的xmlns = “http://tempuri.org/”)

SELECT a.b.value('@CustCode','varchar(4)') AS Code
   ,a.b.value('CustName[1]','varchar(20)') AS Name
   ,c.d.value('No[1]','int') AS QNo
   ,c.d.value('(Line)[1]','int') AS QLine
FROM  PGHRMS_Employees x
CROSS APPLY x.data.nodes('/ArrayOfCustomerInfo/Customer') AS a(b)
CROSS APPLY a.b.nodes('Queues/Q') AS c(d)

please give me some advice. I've to achieve with SQL SERVER :(

请给我一些建议。我要用SQL SERVER实现:(

If anyone want to reproduce it, I pasted script at : http://pastebin.com/ueZGidyL

如果有人想要重现它,我粘贴脚本:http://pastebin.com/ueZGidyL

Thank you in advance !!!

先谢谢你 !!!

1 个解决方案

#1

Try this:

;WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org/')
SELECT
    Code = XC1.value('@CustCode', 'varchar(4)'),
    Name = XC1.value('CustName[1]', 'varchar(20)'),
    QNo = XC2.value('No[1]', 'int') ,
    QLine = XC2.value('(Line)[1]','int') 
FROM
    PGHRMS_Employees 
CROSS APPLY
    XmlContent.nodes('/ArrayOfCustomerInfo/Customer') AS XT1(XC1)
CROSS APPLY 
    XC1.nodes('Queues/Q') AS XT2(XC2)

With the WITH XMLNAMESPACES construct, you can define some XML namespaces to be used by the following T-SQL statement - default or prefixed namespaces alike.

使用WITH XMLNAMESPACES构造,您可以定义一些XML命名空间以供以下T-SQL语句使用 - 默认或带前缀的命名空间。