How to Get Specific Xml Namespace in Xquery in SQL Server

sql server parse xml with namespace

Try it like this:

--your declaration

declare @text varchar(max)

set @text = N'
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Body xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <queryEE xmlns="http://xx.gob.gcaba.xx/">
      <codeEE xmlns="">xxxx</codeEE>
    </queryEE>
  </s:Body>
</s:Envelope>'

declare @x xml

set @x = cast(@text as xml);

--The query

WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS s
                  ,'http://xx.gob.gcaba.xx/' AS innerDflt) 
select @x.value('(/s:Envelope/s:Body/innerDflt:queryEE/codeEE/text())[1]','nvarchar(100)');

Some background:

Your XML is a bit weird looking at the namespaces... if the construction is under your control, it would be worth to start here.

There is a namespace s: to define <Envelope> and <Body>. That is fine so far., But then the element <queryEE> defines a default namespace (no prefix!) and the embedded <codeEE> defines another (but empty!) default namespace. I'm pretty sure, that this empty namespaces is created within a query by combining XMLs together...

The default namespace tells the engine, that all nodes without a specific prefix are living within this namespace. So we have to address that.

My code is using WITH XMLNAMESPACES to declare all namespaces occuring in the XML. Different to the original XML I define a prefix (innerDflt) for the first defualt namespace. That means, we can address <innerDflt:queryEE>. The embedded element does not need a namespace. It is living within an empty default (=> no) namespace.

All this said, I just want to point out, that you can use a wildcard too:

select @x.value('(/*:Envelope/*:Body/*:queryEE/*:codeEE/text())[1]','nvarchar(100)')

And you might even use a deep search

select @x.value('(//*:codeEE/text())[1]','nvarchar(100)')

But the general advise is: Be as specific as possible.

SQL Server XML namespace colon

Minimal typo - in the .value(), do not include the leading '/':

WITH XMLNAMESPACES(N'http://schemas.fortum.com/amm/delivermeterreading' AS del)
SELECT
    t.file_name, t.file_created_time received_timestamp,
    h.value(N'(del:MessageId)[1]', 'varchar(40)')
FROM
    load.t t
OUTER APPLY
    t.xml_data.nodes('/del:DeliverMeterReading/del:Header') AS m(h)

SQL Server XQuery with Default Namespace

If namespaces are not important in your use case, you could use the namespace wildcard selector *:, which both selects nodes without and with arbitrary namespaces.

An example query could be

(/*:AffordabilityResults/*:matchlevel)[1]

The business analyst will still have to add the selector in front of every node test, but it's the same "prefix" all the time and the only error to be expected is forgetting to use it somewhere.

XPath/XQuery - getting the field with namespace attributes out as a value/query?

Thanks for adding a sample to your XML.

Might be that this does not cover all your needs, but I think you might walk this route:

Attention: I'm assuming, that there is just one occurance of any event id per XML.

Your sample mockup table. I added two rows, one having event 53 and one not.

DECLARE @xml TABLE (id int IDENTITY, switch_xml XML)
INSERT INTO @xml (switch_xml)
VALUES 
 ('<MAIN> <!-- event 53 exists -->
    <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <event>53</event>
      <myfield>string o text</myfield>
      <myfield2>some other string</myfield2>
    </Secondary>
    <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <event>56</event>
      <myfield>different string o tet</myfield>
      <myfield2>and some other other strings</myfield2>
    </Secondary>
    </MAIN>')

,(N'<MAIN> <!-- no event 53 here!!! -->
    <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <event>50</event>
      <myfield>string o text</myfield>
      <myfield2>some other string</myfield2>
    </Secondary>
    <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <event>56</event>
      <myfield>different string o tet</myfield>
      <myfield2>and some other other strings</myfield2>
    </Secondary>
    </MAIN>');

--using parameters makes life easier

DECLARE @SearchForEvent INT = 53;
DECLARE @ChangeThisEvent INT = 56;
DECLARE @ReplaceWith VARCHAR(100)='Some replacement';

--We can use .modify() to update the needed value

UPDATE @xml SET switch_xml.modify('replace value of (/MAIN[Secondary[event=sql:variable("@SearchForEvent")]]
                                                     /Secondary[event=sql:variable("@ChangeThisEvent")]
                                                     /myfield
                                                     /text())[1] with sql:variable("@ReplaceWith")');

--Check the result

SELECT * FROM @xml;

The first XML looks like this:

<MAIN>
  <!-- event 53 exists -->
  <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <event>53</event>
    <myfield>string o text</myfield>
    <myfield2>some other string</myfield2>
  </Secondary>
  <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <event>56</event>
    <myfield>Some replacement</myfield>
    <myfield2>and some other other strings</myfield2>
  </Secondary>
</MAIN>

... and the second like this

<MAIN>
  <!-- no event 53 here!!! -->
  <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <event>50</event>
    <myfield>string o text</myfield>
    <myfield2>some other string</myfield2>
  </Secondary>
  <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <event>56</event>
    <myfield>different string o tet</myfield>
    <myfield2>and some other other strings</myfield2>
  </Secondary>
</MAIN>

The idea in short:

We tell .modify() via XPath what to change
The Xpath will dive into <MAIN> if there is a <Secondary>, which has a <event> element with the value as given (=53)
if this holds true we dive below <Main> into <Secondary>, searching for an element where <event> has the other given value (=56)
If found, we can replace this with the third given value.

Parsing XML with namespaces in SQL Server

Since your top-level node <TravelItineraryReadRS> is not part of that XML namespace, you cannot use the DEFAULT qualifier. Instead, you have to define a XML namespace prefix, and include that in your XQuery:

;WITH XMLNAMESPACES ('http://webservices.sabre.com/sabreXML/2011/10' AS ns)
SELECT
    XmLContent.value('(/TravelItineraryReadRS/ns:TravelItinerary/ns:CustomerInfo/ns:PersonName[1]/ns:GivenName)[1]', 'nvarchar(50)') AS Name
FROM 
    dbo.RezMonitorXMLdataTest 
WHERE
    locator = 'GUBXRV'

Replacing xml namespace prefixes in SQL Server with XQuery

If you would like to replace it with space

update tbl_Archive set XML.modify('delete /*:Document/*:Volume[text()]/@xmlns:sg')