How to get value of a node with namespace prefix [duplicate]

Question

So, I have got the following query that I tried to get the value between the nodes:

<oas:Base64Assertion>235fkl53</oas:Base64Assertion>

I want to extract the value 235fkl53. I tried with the following query:

declare @x xml = '
  <soapenv:Header>
    <oas:Security>
      <oas:Base64Assertion>
        <oas:Security xmlns:oas="http://example.com">
          <oas:Base64Assertion>235fkl53</oas:Base64Assertion>
        </oas:Security>
      </oas:Base64Assertion>
    </oas:Security>
  </soapenv:Header>
';

select cast(@x.value('(//oas:Base64Assertion)[1]', 'nvarchar(max)') as xml).query('//value/text()') as cdata;

But I get the following error:

Msg 2229, Level 16, State 1, Line 13 XQuery [value()]: The name "oas" does not denote a namespace.

Any tips on how to achieve this?

@SalmanA Sorry I did not understand your question. What is it that you are asking? — Rahul Sharma
– Rahul Sharma, Commented Mar 15, 2022 at 19:55
Your xml is rejected by sql server (dbfiddle.uk/…). are there any elements before the <soapenv:Header>? — Salman Arshad
– Salman Arshad, Commented Mar 15, 2022 at 20:01
@SalmanA Isn't the above XML valid? There are more elements but will that make any difference ? — Rahul Sharma
– Rahul Sharma, Commented Mar 15, 2022 at 20:06
Side points: // descendant axis is slow, it's better to just put the whole path in. Looks like you actually have Base64, so you can change your data type to varbinary and it will decode it for you. And max probably isn't necessary unless you think it will be over 8kb long. Finally you don't need to use both value and query, you can just do @x.value('(/soapenv:Header/oas:Security/oas:Base64Assertion/oas:Security/oas:Base64Assertion/text())[1]', 'varbinary(100)'). — Charlieface
– Charlieface, Commented Mar 15, 2022 at 22:13

Salman Arshad · Accepted Answer · 2022-03-15 20:27:22Z

2

You did not add the namespaces in the XML you posted so I have added them in the following example based on where I think they should be.

You need to add WITH NAMESPACE before using namespace prefixes in XQueries:

declare @x xml = '<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope" xmlns:oas="http://example.com">
  <soapenv:Header>
    <oas:Security>
      <oas:Base64Assertion>
        <oas:Security>
          <oas:Base64Assertion>235fkl53</oas:Base64Assertion>
        </oas:Security>
      </oas:Base64Assertion>
    </oas:Security>
  </soapenv:Header>
</soapenv:Envelope>
';

WITH XMLNAMESPACES ('http://example.com' AS oas)
SELECT @x.value('(//oas:Base64Assertion)[1]', 'nvarchar(max)');

SELECT @x.value('declare namespace oas="http://example.com"; (//oas:Base64Assertion)[1]', 'nvarchar(max)');

DB<>Fiddle

edited Mar 15, 2022 at 20:27

answered Mar 15, 2022 at 20:20

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

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

Collectives™ on Stack Overflow

How to get value of a node with namespace prefix [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related