如何使用存盤程序轉換XML文本并插入到MicrosoftSQLServer

我有一個這樣的 XML 檔案：

<?xml version=\"1.0\" encoding=\"utf-8\"?>
<soap:Envelope xmlns:soap=\"http://www.w3.org/2003/05/soap-envelope\" 
               xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" 
               xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">
    <soap:Body>
        <Abc ">
            <Def>ID&gt;1&lt;/ID&gt;&lt;Number&gt;Not Available&lt;/Number&gt;&lt;AbcName&gt;Hello&lt;/AbcName&</Def>
        </Abc>
    </soap:Body>
</soap:Envelope>

我想撰寫一個存盤程序，提取DEF標記內的文本并獲取列資料，如 Id、abcname 并插入到 SQL Server 資料庫中。

對這個問題的任何幫助將不勝感激。

謝謝！

uj5u.com熱心網友回復：

提取<Def>as字串的值，然后決議為xml，由于xml沒有根節點，會添加一個 節點，所以我們可以在 sql server 中使用 xml 函式來獲取節點值。

declare @xmlstring varchar(max) 
declare @xml xml
set @xmlstring = N'<?xml version=\"1.0\" encoding=\"utf-8\"?>
<soap:Envelope xmlns:soap=\"http://www.w3.org/2003/05/soap-envelope\" 
               xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" 
               xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">
    <soap:Body>
        <Abc xmlns=\"http://www.web.xds.co.za/XDSConnectWS\">
            <Def>&lt;ID&gt;1&lt;/ID&gt;&lt;Number&gt;Not Available&lt;/Number&gt;&lt;AbcName&gt;Hello&lt;/AbcName&gt;</Def>
        </Abc>
    </soap:Body>
</soap:Envelope>';

set @xmlstring = substring(@xmlstring, charindex('<Def>', @xmlstring) 5, charindex('</Def>', @xmlstring) - charindex('<Def>', @xmlstring) -5)

set @xml = concat('<root>', cast (@xmlstring as xml).value('.[1]','nvarchar(max)' ), '</root>');

select @xml;
select @xml.value('(/root/ID)[1]','varchar(30)') as ID
    ,  @xml.value('(/root/AbcName)[1]','varchar(30)') as [Name]

uj5u.com熱心網友回復：

這里的問題是里面的 XML 節點Def沒有正確編碼。它們存盤為節點內的內部文本。所以你需要把它拉出來并轉換它。

我們可以使用.value和CASTit 在一個CROSS APPLY (VALUES.

您可以使用直接.value呼叫來執行此操作

DECLARE @xml xml = '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" 
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
               xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <Abc xmlns="http://www.web.xds.co.za/XDSConnectWS">
            <Def>&lt;ID&gt;1&lt;/ID&gt;&lt;Number&gt;Not Available&lt;/Number&gt;&lt;AbcName&gt;Hello&lt;/AbcName&gt;</Def>
        </Abc>
    </soap:Body>
</soap:Envelope>';

WITH XMLNAMESPACES(
  'http://www.web.xds.co.za/XDSConnectWS' AS x,
  'http://www.w3.org/2003/05/soap-envelope' AS soap
)
SELECT
  v.InnerXml.value('(ID/text())[1]','int') as ID,
  v.InnerXml.value('(AbcName/text())[1]','varchar(30)') as Name,
  v.InnerXml.value('(Number/text())[1]','varchar(30)') as Number
FROM (VALUES(
    CAST(@xml.value('(soap:Envelope/soap:Body/x:Abc/x:Def/text())[1]','nvarchar(max)') AS xml)
)) v(InnerXml);

或者，如果您有多個節點要讀取，則可以通過以下方式提供.nodes：

WITH XMLNAMESPACES(
  'http://www.web.xds.co.za/XDSConnectWS' AS x,
  'http://www.w3.org/2003/05/soap-envelope' AS soap
)
SELECT
  x2.n.value('(ID/text())[1]','int') as ID,
  x2.n.value('(AbcName/text())[1]','varchar(30)') as Name,
  x2.n.value('(Number/text())[1]','varchar(30)') as Number
FROM @xml.nodes('soap:Envelope/soap:Body/x:Abc/x:Def') x1(DEF)
CROSS APPLY (VALUES(
    CAST(N'<r>'   x1.DEF.value('text()[1]','nvarchar(max)')   '</r>' AS xml)
)) v(InnerXml)
CROSS APPLY v.InnerXml.nodes('r') x2(n);

資料庫<>小提琴

標籤：sql sql-server xml 存储过程 肥皂

上一篇：將子查詢轉換為JSON性能

下一篇：在Perl中從XML獲取XSI型別