Parse data from XML with namespace

Question

I'm trying to parse the XML data coming from the SQL Server. But although I tried many ways, I could not succeed.

When I run the script it does not give an error but returns a not record

DECLARE @xmlData XML

SET @xmlData = '<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
  <S:Body>
    <S:Fault xmlns:ns4="http://www.w3.org/2003/05/soap-envelope">
      <faultcode>S:VersionMismatch</faultcode>
      <faultstring>Couldnt create SOAP message. Expecting Envelope in namespace http://schemas.xmlsoap.org/soap/envelope/, but got null </faultstring>
    </S:Fault>
  </S:Body>
</S:Envelope>
'

SET @xmlData = (SELECT @xmlData.query('declare default element namespace "http://schemas.xmlsoap.org/soap/envelope/";
/Envelope/Body'))

SELECT @xmlData

SELECT b.value('(./Fault/faultcode/text())[1]', 'Varchar(50)') AS [Name]
  FROM @xmlData.nodes('/Body') AS a (b)

Lukasz Szozda · Accepted Answer · 2020-12-29 16:39:17Z

0

Using WITH XMLNAMESPACES:

;WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS S)
SELECT b.value('(./S:Fault/faultcode/text())[1]', 'Varchar(50)') AS [Name]
  FROM @xmlData.nodes('//S:Body') AS a (b);

db<>fiddle demo

answered Dec 29, 2020 at 16:39

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parse data from XML with namespace

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related