Get values in XML nodes using SQL

Question

I have an XML in SQL table column. I need to decode this xml and get value of particular nodes. Find my XML below

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP-ENV:Header/>
    <SOAP-ENV:Body>
        <ns:createTransactionResponse
            xmlns:impl="http://office/work/services/service1"
            xmlns:ns="http://www.regfrez.com/schemas/service1_V2/SharedResources/XMLSchema/Schema.xsd"
            xmlns:tns="http://www.erdeftq.ae/Activematrix/ESB/service1/1_0">
            <transactionResponse>
                <transaction-info>
                    <registrationId>R1234</registrationId>
                    <trialId>T12345</trialId>
                    <transactionId>12345</transactionId>
                    <transactionDate>27-02-2020:08:47</transactionDate>
                    <status>Confirmed</status>
                </transaction-info>
            </transactionResponse>
        </ns:createTransactionResponse>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

I need the values of nodes: registrationId, transactionId and status and I tried this but couldn't succeed as i got empty value as result:

DECLARE @xml XML
SET @xml = 'XML here'
SELECT T.C.value('@status', 'nvarchar(100)') FROM @xml.nodes('createTransactionResponse/transactionResponse/transaction-info/status') T(C)
SELECT T.C.value('@trans', 'nvarchar(100)') FROM @xml.nodes('createTransactionResponse/transactionResponse/transaction-info/transactionId') T(C)
SELECT T.C.value('@id', 'nvarchar(100)') FROM @xml.nodes('createTransactionResponse/transactionResponse/transaction-info/registrationId') T(C)

Any help/correction would be appreciated

Ideally, you should have editted your previous question and waited for it to be reopened, however, you have now shown your attempts, which is great. — Thom A
– Thom A ♦, Commented Feb 27, 2020 at 12:29
@Larnu Please suggest the resolution, if you have some info. — Aditya Prasad
– Aditya Prasad, Commented Feb 27, 2020 at 12:32
Any other way around rather than using XML type? I need w.r.to NVARCHAR datatype since this xml is stored in column of type NVARCHAR(MAX) — Aditya Prasad
– Aditya Prasad, Commented Feb 27, 2020 at 13:25
If you're not storing XML data as xml you can't treat it as XML, it's as simple as that. Fix the data type. — Thom A
– Thom A ♦, Commented Feb 27, 2020 at 13:28

Gottfried Lesigang · Accepted Answer · 2020-02-27 12:39:28Z

2

Your own attempt is ignoring the namespaces and does not specify the full XPath.

Try one of these approaches:

Your XML:

DECLARE @xml XML
SET @xml = '<SOAP-ENV:Envelope
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP-ENV:Header/>
    <SOAP-ENV:Body>
        <ns:createTransactionResponse
            xmlns:impl="http://office/work/services/service1"
            xmlns:ns="http://www.regfrez.com/schemas/service1_V2/SharedResources/XMLSchema/Schema.xsd"
            xmlns:tns="http://www.erdeftq.ae/Activematrix/ESB/service1/1_0">
            <transactionResponse>
                <transaction-info>
                    <registrationId>R1234</registrationId>
                    <trialId>T12345</trialId>
                    <transactionId>12345</transactionId>
                    <transactionDate>27-02-2020:08:47</transactionDate>
                    <status>Confirmed</status>
                </transaction-info>
            </transactionResponse>
        </ns:createTransactionResponse>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>';

--This is the most explicit (which is always a best way):

WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS n1
                  ,'http://www.regfrez.com/schemas/service1_V2/SharedResources/XMLSchema/Schema.xsd' AS n2)
SELECT @xml.value('(/n1:Envelope/n1:Body/n2:createTransactionResponse/transactionResponse/transaction-info/registrationId/text())[1]','nvarchar(max)') AS RegistrationId
      ,@xml.value('(/n1:Envelope/n1:Body/n2:createTransactionResponse/transactionResponse/transaction-info/transactionId/text())[1]','nvarchar(max)') AS TransactionId
      ,@xml.value('(/n1:Envelope/n1:Body/n2:createTransactionResponse/transactionResponse/transaction-info/status/text())[1]','nvarchar(max)') AS [Status];

--This will avoid some repeated XPath, but .nodes() produces quite some overhead:

WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS n1
                  ,'http://www.regfrez.com/schemas/service1_V2/SharedResources/XMLSchema/Schema.xsd' AS n2)
SELECT ti.value('(registrationId/text())[1]','nvarchar(max)') AS RegistrationId
      ,ti.value('(transactionId/text())[1]','nvarchar(max)') AS TransactionId
      ,ti.value('(status/text())[1]','nvarchar(max)') AS [Status]
FROM @xml.nodes('/n1:Envelope/n1:Body/n2:createTransactionResponse/transactionResponse/transaction-info') A(ti);

--And this is for lazy people :-)

SELECT @xml.value('(//*:registrationId)[1]','nvarchar(max)') AS RegistrationId
      ,@xml.value('(//*:transactionId)[1]','nvarchar(max)') AS TransactionId
      ,@xml.value('(//*:status)[1]','nvarchar(max)') AS [Status];

Hint: The last one (for lazy people) uses the deep search (with //) and uses a wildcard for the namespace. This is very dangerous if the elements might occur more than once within your XML.

answered Feb 27, 2020 at 12:39

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

3 Comments

Aditya Prasad Over a year ago

Is there anyway around using VARCHAR type rather than XML dataType? Please help

Gottfried Lesigang Over a year ago

@AdityaPrasad It is a very bad approach to store XML in a string type. If possible, you should change that. If not, you'll have to cast your string. In this case I'd suggest to use the second approach with .nodes(), which would need the cast just once.

Yitzhak Khabinsky Over a year ago

Good answer, +1 from my side!

Thom A · Accepted Answer · 2020-02-27 12:39:30Z

The XML you have is pretty complex. You have multiple namespaces, with different nodes using different ones. This means you need to use WITH XMLNAMESPACES to declare all these.

Then you need to use nodes to navigate the to needed node, prefixing them with the appropriate namespaces, till you get to transaction-info. Then you use use value to get the information.

@Status isn't what you're after here, that's for is you have something like <node status=1\>, you need to get the text() value of the node.

This results in the below:

DECLARE @X xml = '<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP-ENV:Header/>
    <SOAP-ENV:Body>
        <ns:createTransactionResponse
            xmlns:impl="http://traffic2/traffic/services/service1"
            xmlns:ns="http://www.regfrez.com/schemas/service1_V2/SharedResources/XMLSchema/Schema.xsd"
            xmlns:tns="http://www.abc.ae/Activematrix/ESB/service1/1_0">
            <transactionResponse>
                <transaction-info>
                    <registrationId>R1234</registrationId>
                    <trialId>T12345</trialId>
                    <transactionId>12345</transactionId>
                    <transactionDate>27-02-2020:08:47</transactionDate>
                    <status>Confirmed</status>
                </transaction-info>
            </transactionResponse>
        </ns:createTransactionResponse>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>';

WITH XMLNAMESPACES ('http://schemas.xmlsoap.org/soap/envelope/' AS [SOAP-ENV],
                    'http://traffic2/traffic/services/service1' AS impl, --YOu don't use this in the XML, but incldued anyway, for completeness
                    'http://www.regfrez.com/schemas/service1_V2/SharedResources/XMLSchema/Schema.xsd' AS ns,
                    'http://www.abc.ae/Activematrix/ESB/service1/1_0' AS tns) --YOu don't use this in the XML, but incldued anyway, for completeness
SELECT ti.value('(status/text())[1]','varchar(10)') AS [Status],
       ti.value('(transactionId/text())[1]','int') AS Trans,
       ti.value('(registrationId/text())[1]','varchar(10)') AS ID
FROM @X.nodes('SOAP-ENV:Envelope/SOAP-ENV:Body/ns:createTransactionResponse/transactionResponse/transaction-info') N(ti);

Collectives™ on Stack Overflow

Get values in XML nodes using SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related