Блог

 <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>
    <GetLoginResponse xmlns="http://example.com">
        <GetLoginResult>
            <xs:schema id="GetLoginIDResponse" targetNamespace="http://example.com/GetLoginIDResponse.xsd" xmlns:mstns="http://example.com/GetLoginIDResponse.xsd" xmlns="http://example.com/GetLoginIDResponse.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">
                <xs:element name="GetLoginIDResponse" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
                    <xs:complexType>
                        <xs:choice minOccurs="0" maxOccurs="unbounded">
                            <xs:element name="GetLoginIDResult">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element name="LoginID" type="xs:string" minOccurs="0" />
                                        <xs:element name="Status" 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">
                <GetLoginIDResponse xmlns="http://example.com/GetLoginIDResponse.xsd">
                    <GetLoginIDResult diffgr:id="GetLoginIDResult1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
                        <LoginID>123456</LoginID>
                        <Status>SUCCESS</Status>
                    </GetLoginIDResult>
                </GetLoginIDResponse>
            </diffgr:diffgram>
        </GetLoginResult>
    </GetLoginResponse>
</soap:Body>
</soap:Envelope>
  

Как я могу получить только LoginID и Status отдельно? Он отображается только LoginID Status в столбце и. Как его можно разделить?

Единственная сложность заключается в том, чтобы позаботиться о нескольких пространствах имен. Вы можете попробовать следующее решение.

SQL

 DECLARE @xml XML = N'<?xml version="1.0"?>
<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>
        <GetLoginResponse xmlns="http://example.com">
            <GetLoginResult>
                <xs:schema id="GetLoginIDResponse"
                           targetNamespace="http://example.com/GetLoginIDResponse.xsd"
                           xmlns:mstns="http://example.com/GetLoginIDResponse.xsd"
                           xmlns="http://example.com/GetLoginIDResponse.xsd"
                           xmlns:xs="http://www.w3.org/2001/XMLSchema"
                           xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
                           attributeFormDefault="qualified"
                           elementFormDefault="qualified">
                    <xs:element name="GetLoginIDResponse"
                                msdata:IsDataSet="true"
                                msdata:UseCurrentLocale="true">
                        <xs:complexType>
                            <xs:choice minOccurs="0" maxOccurs="unbounded">
                                <xs:element name="GetLoginIDResult">
                                    <xs:complexType>
                                        <xs:sequence>
                                            <xs:element name="LoginID"
                                                        type="xs:string"
                                                        minOccurs="0"/>
                                            <xs:element name="Status"
                                                        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">
                    <GetLoginIDResponse xmlns="http://example.com/GetLoginIDResponse.xsd">
                        <GetLoginIDResult diffgr:id="GetLoginIDResult1"
                                          msdata:rowOrder="0"
                                          diffgr:hasChanges="inserted">
                            <LoginID>123456</LoginID>
                            <Status>SUCCESS</Status>
                        </GetLoginIDResult>
                    </GetLoginIDResponse>
                </diffgr:diffgram>
            </GetLoginResult>
        </GetLoginResponse>
    </soap:Body>
</soap:Envelope>';

;WITH XMLNAMESPACES ('http://schemas.xmlsoap.org/soap/envelope/' AS soap
    , 'http://example.com' AS ns1, 'urn:schemas-microsoft-com:xml-diffgram-v1' AS diffgr
    , DEFAULT 'http://example.com/GetLoginIDResponse.xsd')
SELECT c.value('(LoginID/text())[1]','VARCHAR(30)') AS [LoginID]
    , c.value('(Status/text())[1]','VARCHAR(30)') AS [Status]
FROM @xml.nodes('/soap:Envelope/soap:Body/ns1:GetLoginResponse/ns1:GetLoginResult/diffgr:diffgram/GetLoginIDResponse/GetLoginIDResult') AS t(c);
  

Вывод

  --------- --------- 
| LoginID | Status  |
 --------- --------- 
|  123456 | SUCCESS |
 --------- ---------