如何在SQL Server中解析SOAP XML并显示为表数据 [英] How to parse SOAP XML in SQL Server and show as table data
问题描述
您好我在SOAP XML中有示例数据
Hi I have sample data in SOAP XML
DECLARE
@ xml XML =
'< soapenv:Envelope xmlns:env =" http://schemas.xmlsoap.org/soap/envelope/"的xmlns:soapenv = QUOT; HTTP://schemas.xmlsoap.org/soap/envelope/">
'<soapenv:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
&NBSP;&NBSP;&NBSP; < soap:Header xmlns:soap =" http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
< ns0:Context xmlns:ns0 =" http://service.PUBG.com/entity/message/2007/"的xmlns:SOAP = QUOT; HTTP://schemas.xmlsoap.org/soap/envelope/"的xmlns:soapenv = QUOT; HTTP://schemas.xmlsoap.org/soap/envelope/">
<ns0:Context xmlns:ns0="http://service.PUBG.com/entity/message/2007/" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP; < MESSAGEID>
<messageId>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < N455>< / N455>
<N455></N455>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < / MESSAGEID>
</messageId>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < refToMessageId> 142323< / refToMessageId>
<refToMessageId>142323</refToMessageId>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; <时间戳> 2018-08-08T23:00:11.282Z< /时间戳>
<Timestamp>2018-08-08T23:00:11.282Z</Timestamp>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; <的applicationID> PUBG< /的applicationID>
<applicationId>PUBG</applicationId>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; <主机名>未知< /主机名>
<hostName>UNKNOWN</hostName>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < / NS0:语境GT;
</ns0:Context>
&NBSP;&NBSP;&NBSP; < /皂:抬头>
</soap:Header>
&NBSP;&NBSP;&NBSP; < soap:Body xmlns:soap =" http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
< ns2:searchResponseResponse xmlns:ns2 =" http://service.PubG.com/provider/gpow/bankLookup/PubGLookUpService/2013/"的xmlns:NS4 = QUOT; HTTP://service.PubG.com/provider/gpow/bankLookup/PubGLookUpService/2015/">
<ns2:searchResponseResponse xmlns:ns2="http://service.PubG.com/provider/gpow/bankLookup/PubGLookUpService/2013/" xmlns:ns4="http://service.PubG.com/provider/gpow/bankLookup/PubGLookUpService/2015/">
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP; < NS4:PubGDetails>
<ns4:PubGDetails>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < ns4:名称> PUBG Roll< / ns4:姓名>
<ns4:Name>PUBG Roll</ns4:Name>
< / NS4:PubGDetails>
</ns4:PubGDetails>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < NS4:PubGCode> XXXXXXX< / NS4:PubGCode>
<ns4:PubGCode>xxxxxxx</ns4:PubGCode>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < NS4:COUNTRYCODE> US< / NS4:COUNTRYCODE>
<ns4:countryCode>US</ns4:countryCode>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < NS4:地址>
<ns4:address>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < ns4:physicalAddress1> XXX PARK AVENUE< / ns4:physicalAddress1>
<ns4:physicalAddress1>XXX PARK AVENUE</ns4:physicalAddress1>
< / NS4:地址>
</ns4:address>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < NS4:邮政编码> 123456< / NS4:邮政编码>
<ns4:zipCode>123456</ns4:zipCode>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < NS4:国家名称>美国< / NS4:国家名称>
<ns4:countryName>USA</ns4:countryName>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < NS4:parentPubGCode大于0< / NS4:parentPubGCode>
<ns4:parentPubGCode>0</ns4:parentPubGCode>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < NS4:modificationInd>一种与LT; / NS4:modificationInd>
<ns4:modificationInd>A</ns4:modificationInd>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < NS4:时间modificationdate> 2017年5月2日和LT; / NS4:时间modificationdate>
<ns4:modificationDate>2017-05-02</ns4:modificationDate>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < / NS2:searchResponseResponse>
</ns2:searchResponseResponse>
&NBSP;&NBSP;&NBSP; < /皂:车身与GT;
</soap:Body>
< / soapenv:信封>'
</soapenv:Envelope>'
;
我写了这样的代码 获取表格格式 :
I have written code like this to get in table format :
SELECT r < span style ="color:#808080; font-size:small">。 值 ( 'messageId [1]' , 'varchar(max)' ) AS messageId
SELECTr.value('messageId[1]','varchar(max)')ASmessageId
, r 。 value ( 'refToMessageId [1]' , 'varchar(max)' ) AS refToMessageId
,r.value('refToMessageId[1]','varchar(max)')ASrefToMessageId
, r 。 value ( 'Timestamp [1]' , 'datetime' ) AS 时间戳
,r.value('Timestamp[1]','datetime')ASTimestamp
, r 。 value ( 'applicationId [1]' , 'varchar(max)' ) AS applicationId
,r.value('applicationId[1]','varchar(max)')ASapplicationId
, r 。 value ( 'hostName [1]' , 'varchar(max)' ) AS hostName
,r.value('hostName[1]','varchar(max)')AShostName
, r 。 value ( 'PubGDetails [1]' , 'varchar(max)' ) AS PubGDetails
,r.value('PubGDetails[1]','varchar(max)')ASPubGDetails
, r 。 value ( 'CityHeading [1]' , 'varchar(max)' ) AS CityHeading
,r.value('CityHeading[1]','varchar(max)')ASCityHeading
, r 。 value ( 'PubGCode [1]' , 'varchar(max)' ) AS PubGCode
,r.value('PubGCode[1]','varchar(max)')ASPubGCode
, r 。 value ( 'countryCode [1]' , 'varchar(max)' ) AS countryCode
,r.value('countryCode[1]','varchar(max)')AScountryCode
, r 。 value ( 'PubGId [1]' , 'int' ) AS PubGCode
,r.value('PubGId[1]','int')ASPubGCode
, r 。 value ( 'address [1]' , 'varchar(max)' ) AS PubGCode
,r.value('address[1]','varchar(max)')ASPubGCode
, r 。 value ( 'zipCode [1]' , 'int' ) AS PubGCode
,r.value('zipCode[1]','int')ASPubGCode
, r 。 value ( 'countryName [1]' , 'varchar(max)' ) AS PubGCode
,r.value('countryName[1]','varchar(max)')ASPubGCode
, r 。 value ( 'parentPubGCode [1]' , 'int' ) AS PubGCode
,r.value('parentPubGCode[1]','int')ASPubGCode
, r 。 value ( 'modificationInd [1]' , 'varchar(2)' ) AS PubGCode
,r.value('modificationInd[1]','varchar(2)')ASPubGCode
, r 。 value ( 'modificationDate [1]' , 'DateTime' ) AS PubGCode
,r.value('modificationDate[1]','DateTime')ASPubGCode
FROM
@ xml 。 节点 ( '/ *:Envelope / *:Body / *:row' ) AS A ( r )
@xml.nodes('/*:Envelope/*:Body/*:row')ASA(r)
如何得到这样的输出:
推荐答案
Good day mohan,
Your question is not related to SQL Server but to XML format. your text is not a valid XML which lead to the issue in the SQL Server side.
Your question is not related to SQL Server but to XML format. your text is not a valid XML which lead to the issue in the SQL Server side.
First make sure that you are using valid XML using one of the online Formatter
like this one.
First make sure that you are using valid XML using one of the online Formatter like this one.
Here are some issue in your text which make is NON-XML content:
Here are some issue in your text which make is NON-XML content:
<soapenv:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
You forgot to close the tag with ">"
You forgot to close the tag with ">"
You have this tag: <N455d> without a closer </N455d>
You have this tag: <N455d> without a closer </N455d>
<ns4:Name> without closer
<ns4:Name> without closer
and so on, and so on....
and so on, and so on....
Note! You can use TRY_CONVERT in order to confirm if the text is valud XML but thuis will not give you the reasons of the issue but only a "mark" that the text is not valid to convert to XML
Note! You can use TRY_CONVERT in order to confirm if the text is valud XML but thuis will not give you the reasons of the issue but only a "mark" that the text is not valid to convert to XML
DECLARE @string NVARCHAR(MAX)=
'<soapenv:Envelope
xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
<soap:Header xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<ns0:Context xmlns:ns0="http://service.PUBG.com/entity/message/2007/">
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
<messageId><N455d></messageId>
<refToMessageId>142323</refToMessageId>
<Timestamp>2018-08-08T23:00:11.282Z</Timestamp>
<applicationId>PUBG</applicationId>
<hostName>UNKNOWN</hostName>
</ns0:Context></soap:Header>
<soap:Body xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<ns2:searchResponseResponse xmlns:ns2="http://service.PubG.com/provider/gpow/bankLookup/PubGLookUpService/2013/">
<ns4:PubGDetails><ns4:Name>PUBG Roll</ns4:PubGDetails>
<ns4:PubGCode>xxxxxxx</ns4:PubGCode>
<ns4:countryCode>US</ns4:countryCode>
<ns4:address><ns4:physicalAddress1>XXX PARK AVENUE</ns4:physicalAddress1></ns4:address>
<ns4:zipCode>123456</ns4:zipCode>
<ns4:countryName>USA</ns4:countryName>
<ns4:parentPubGCode>0</ns4:parentPubGCode>
<ns4:modificationInd>A</ns4:modificationInd>
<ns4:modificationDate>2017-05-02</ns4:modificationDate>
</ns2:searchPubGResponse>
</soap:Body>
</soapenv:Envelope>';
select TRY_CONVERT (XML, @String)
这篇关于如何在SQL Server中解析SOAP XML并显示为表数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!