如何在sql server中解析SOAP xml并显示为表格式

Question

Web服务返回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>[e-mail]</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

如何获得这样的输出：|代码|成员代码||----|--------||0|9997|

请帮忙提供建议。谢谢

Answer 1

没有答案。。。。

我相信这与MemberInformationResponse在另一个名称空间中有关

我认为更新的方法是使用XML数据类型的方法

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms190798(v=sql.90(？重定向自=MSDN

而不是sp_xml_preparedocument等

如果你想坚持使用sp_xml_preparedocument，我建议你尝试一下。它产生一个输出，你可以反复地对它进行实验

declare @xmldata VARCHAR(8000)
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>
<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>[e-mail]</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 @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 *
from OPENXML(@readdoc,'/soap:Envelope/soap:Body')
EXEC sp_xml_removedocument @readdoc
GO