Fetch namespace from XML object in SQL

Question

Consider the following XML

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Header>
    <Action xmlns="http://schemas.microsoft.com/ws/2005/05/addressing/none" s:mustUnderstand="1">http://xmlns.scania.com/management/messages/v3</Action>
  </s:Header>
  <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Data xmlns="http://xmlns.scania.com/management/schema/messages/v3">
      <Details />
    </Data>
  </s:Body>
</s:Envelope>

I am trying to fetch the namespace value in xmlns value of the tag, under the Envelope. I tried using

XMLObject.value('namespace-uri((/*:Envelope)[1])', 'varchar(100)') This returns the xmlns of the first element. i.e. "http://schemas.xmlsoap.org/soap/envelope/"

I need to drill down to the xmlns of the tag. i.e. "http://xmlns.scania.com/management/schema/messages/v3"

Could someone help me with this, please?

Tried it, but I'm getting an empty string in the response. Maybe I'm doing something wrong? — maverick639, Mar 10 '21 at 11:15
Ok, so played around a bit and I was missing the "**" in the XPATH query. This code below is working : XMLObject.value('namespace-uri((/*:Envelope/*:Body/*:Data)[1])', 'varchar(100)') — maverick639, Mar 10 '21 at 11:17

score 0 · Accepted Answer · answered Mar 10 '21 at 11:18

Here are three ways to select your namespace URI...

declare @xml xml = N'<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Header>
    <Action xmlns="http://schemas.microsoft.com/ws/2005/05/addressing/none" s:mustUnderstand="1">http://xmlns.scania.com/management/messages/v3</Action>
  </s:Header>
  <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Data xmlns="http://xmlns.scania.com/management/schema/messages/v3">
      <Details />
    </Data>
  </s:Body>
</s:Envelope>';

-- Avoid this: wildcard namespaces are slow...
select Envelope.value('namespace-uri(.)', 'nvarchar(max)') as NamespaceUri
from @xml.nodes('/*:Envelope/*:Body/*:Data') soap(Envelope);

-- Declare namespace(s) inside the XPath expression...
select Envelope.value('namespace-uri(.)', 'nvarchar(max)') as NamespaceUri
from @xml.nodes('declare namespace soap="http://schemas.xmlsoap.org/soap/envelope/";
/soap:Envelope/soap:Body/*:Data') soap(Envelope);

-- Declare namespace(s) before the query (these would be usable in FOR XML as well)...
with xmlnamespaces(
  'http://schemas.xmlsoap.org/soap/envelope/' as soap
)
select Envelope.value('namespace-uri(.)', 'nvarchar(max)') as NamespaceUri
from @xml.nodes('/soap:Envelope/soap:Body/*:Data') soap(Envelope);

Fetch namespace from XML object in SQL

1 Answers1