如何在SQL Server中解析SOAP XML并转换为表格格式

问题场景 #

需要将WebService返回的以下SOAP XML转换为SQL Server中的表格格式：

<?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>
        <MemberInformationResponse xmlns="http://www.ucr.com.hk">
            <MemberInformationResult>
                <Code>0</Code>
                <Description>Get Member Info Success </Description>
                <MemberCode>9997</MemberCode>
                <MemberCardCode>9997</MemberCardCode>
                <MemberTypeCode>GOLD2020</MemberTypeCode>
                <MemberTypeName1>Gold Member 2020</MemberTypeName1>
                <MemberTypeName2>Gold Member 2020</MemberTypeName2>
                <OldMemberTypeCode>SilverLINE</OldMemberTypeCode>
                <OldMemberTypeName1>Silver Member(LINE)</OldMemberTypeName1>
                <OldMemberTypeName2>Silver Member(LINE)</OldMemberTypeName2>
                <LoginID>9997</LoginID>
                <Name1>SilverLINE</Name1>
                <Name2>SilverLINE</Name2>
                <Sex>0</Sex>
                <Mobile>99900099</Mobile>
                <Email>khso@kabu.com.hk</Email>
                <PromotionAlert>1</PromotionAlert>
                <YearOfBirth>2001</YearOfBirth>
                <MonthOfBirth>01</MonthOfBirth>
                <JoinDate>2016/03/01</JoinDate>
                <WorkingDistrict />
                <LivingDistrict />
                <ActivationCode>4516</ActivationCode>
                <ReferralMemberCardCode>0151136201</ReferralMemberCardCode>
                <Enabled>0</Enabled>
                <Point>1005</Point>
                <Point1>1005</Point1>
                <Point2>0</Point2>
                <AccumulatedAmount>16376.8</AccumulatedAmount>
                <ExpiryDate>2020/08/31</ExpiryDate>
                <ExtendExpiryDate>2021/08/31</ExtendExpiryDate>
                <PointRemain>0</PointRemain>
                <PointExpiryDate>----/--/--</PointExpiryDate>
            </MemberInformationResult>
        </MemberInformationResponse>
    </soap:Body>
</soap:Envelope>

尝试了以下脚本但未返回结果：

declare @xmldata xml
SET @xmldata = *put above soap xml*
declare @readdoc as INT

EXEC sp_xml_preparedocument @readdoc OUTPUT, @xmldata , '<root 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" />'

select Code, MemberCode
from OPENXML(@readdoc,'soap:Envelope/soap:Body/MemberInformationResponse/MemberInformationResult')
with
( 
    Code int 'Code',
    MemberCode [varchar](50) 'MemberCode'
)

EXEC sp_xml_removedocument @readdoc
GO

期望输出表格：

解决方案 #

问题根源 #

脚本未声明MemberInformationResponse所属的http://www.ucr.com.hk命名空间，导致XML路径匹配失败，因此无法返回数据。

方法1：修正OPENXML脚本 #

在sp_xml_preparedocument的命名空间参数中添加http://www.ucr.com.hk的命名空间前缀，同时在XPath路径和列映射中使用该前缀：

declare @xmldata xml
SET @xmldata = '<?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>
        <MemberInformationResponse xmlns="http://www.ucr.com.hk">
            <MemberInformationResult>
                <Code>0</Code>
                <MemberCode>9997</MemberCode>
            </MemberInformationResult>
        </MemberInformationResponse>
    </soap:Body>
</soap:Envelope>'
declare @readdoc as INT

-- 添加ucr命名空间声明
EXEC sp_xml_preparedocument @readdoc OUTPUT, @xmldata , '<root xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:ucr="http://www.ucr.com.hk" />'

select Code, MemberCode
from OPENXML(@readdoc,'soap:Envelope/soap:Body/ucr:MemberInformationResponse/ucr:MemberInformationResult')
with
( 
    Code int 'ucr:Code',
    MemberCode [varchar](50) 'ucr:MemberCode'
)

EXEC sp_xml_removedocument @readdoc
GO

方法2：使用XQuery（推荐） #

相比OPENXML，XQuery更简洁且无需手动管理文档句柄，直接通过XML类型的方法解析：

declare @xmldata xml
SET @xmldata = '<?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>
        <MemberInformationResponse xmlns="http://www.ucr.com.hk">
            <MemberInformationResult>
                <Code>0</Code>
                <MemberCode>9997</MemberCode>
            </MemberInformationResult>
        </MemberInformationResponse>
    </soap:Body>
</soap:Envelope>'

;WITH XMLNAMESPACES(
    'http://www.w3.org/2003/05/soap-envelope' AS soap,
    'http://www.ucr.com.hk' AS ucr
)
SELECT
    @xmldata.value('(soap:Envelope/soap:Body/ucr:MemberInformationResponse/ucr:MemberInformationResult/ucr:Code)[1]', 'INT') AS Code,
    @xmldata.value('(soap:Envelope/soap:Body/ucr:MemberInformationResponse/ucr:MemberInformationResult/ucr:MemberCode)[1]', 'VARCHAR(50)') AS MemberCode

两种方法都能得到期望的表格输出。

内容的提问来源于stack exchange，提问作者Winson