Read xml with namesapces

Question

Read xml with namesapces

fafful

SSC Eights!

Points: 810
More actions
September 24, 2019 at 11:54 am

#3682899

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ns1:openSResp xmlns:pf1="http://autogd.NewWebServiceApi.webnew.com/">
<return xmlns:pf2="http://autogd.NewWebServiceApi.webnew.com/">
<pointid>0</pointid>
<newstatus>failed</newstatus>
<ssId>Seasond12453678</ssId>
</return>
</pf1:openSResp></soap:Body>
</soap:Envelope>
I have the response xml above with namespaces, but struggling to read the xml value contained in <ssId>

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

DECLARE @x VARCHAR(4000)
    = '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

<soap:Body>

<ns1:openSResp xmlns:pf1="http://autogd.NewWebServiceApi.webnew.com/">

<return xmlns:pf2="http://autogd.NewWebServiceApi.webnew.com/">

<pointid>0</pointid>

<newstatus>failed</newstatus>

<ssId>Seasond12453678</ssId>

</return>

</pf1:openSResp></soap:Body>

</soap:Envelope>';

SELECT SUBSTRING(@x, CHARINDEX('<ssid>', @x) + 6, CHARINDEX('</ssid>', @x) - CHARINDEX('<ssid>', @x) - 6);

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel