时间戳

首页 > 解决方案 > 在 SQL Server 中读取 XML

sql-server - 在 SQL Server 中读取 XML

问题描述

我正在尝试在 SQL 中阅读下面的 XML。但我总是得到空值。

我将我的 xml 保存在表中EMPweb，然后尝试读取 XML。我的 SQL 代码如下所示。如果可以的话请帮忙。

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <GetEmployeeResponse xmlns="http://tempuri.org/">
      <GetEmployeeResult>
        <xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="Plant">
          <xs:element name="Plant" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
            <xs:complexType>
              <xs:choice minOccurs="0" maxOccurs="unbounded">
                <xs:element name="OrderReleaseAblity">
                  <xs:complexType>
                    <xs:sequence>
                      <xs:element name="BusinessEntityID" type="xs:int" minOccurs="0" />
                      <xs:element name="NationalIDNumber" type="xs:string" minOccurs="0" />
                      <xs:element name="LoginID" type="xs:string" minOccurs="0" />
                      <xs:element name="JobTitle" type="xs:string" minOccurs="0" />
                    </xs:sequence>
                  </xs:complexType>
                </xs:element>
              </xs:choice>
            </xs:complexType>
          </xs:element>
        </xs:schema>
        <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
          <Plant xmlns="">
            <OrderReleaseAblity diffgr:id="OrderReleaseAblity1" msdata:rowOrder="0">
              <BusinessEntityID>275</BusinessEntityID>
              <NationalIDNumber>841560125</NationalIDNumber>
              <LoginID>adventure-works\michael9</LoginID>
              <JobTitle>Sales Representative</JobTitle>
            </OrderReleaseAblity>
          </Plant>
        </diffgr:diffgram>
      </GetEmployeeResult>
    </GetEmployeeResponse>
  </soap:Body>
</soap:Envelope>

SQL查询： 

DECLARE @xDoc XML ; 
Set @xDoc= (Select Data_xml from EMPWeb);

;WITH  XMLNAMESPACES('urn:schemas-microsoft-com:xml-msdata' as msdata,'urn:schemas-microsoft-com:xml-diffgram-v1' as diffgr,default 'http://www.w3.org/2001/XMLSchema'  )

select T.X.value('JobTitle[1]','varchar(500)') AS JobTitle

from @xDoc.nodes('GetEmployeeResponse/GetEmployeeResult/diffgr:diffgram/Plant/OrderReleaseAblity') as T(X)

标签： sql-serversql-server-2008

解决方案

您的 XML 命名空间和.nodes()调用中的 XPath 错误 - 试试这个：

;WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS soap, 
                    'http://tempuri.org/' AS ns, 
                    'urn:schemas-microsoft-com:xml-diffgram-v1' AS dg)
SELECT 
    T.X.value('JobTitle[1]','varchar(500)') AS JobTitle
FROM 
    @xDoc.nodes('/soap:Envelope/soap:Body/ns:GetEmployeeResponse/ns:GetEmployeeResult/dg:diffgram/Plant/OrderReleaseAblity') AS T(X)

推荐阅读