Look at WITH XMLNAMESPACES

;WITH XMLNAMESPACES(DEFAULT 'http://ait.com/reportdata')
SELECT [ReportId]= reportdata.item.value('.', 'varchar(40)') 
FROM   @xVar.nodes('/ReportData/ReportId[1]') AS reportdata(item) 
Answer from Martin Smith on Stack Overflow
🌐
Microsoft Learn
learn.microsoft.com › en-us › sql › relational-databases › xml › add-namespaces-to-queries-with-with-xmlnamespaces
Add Namespaces to Queries with WITH XMLNAMESPACES - SQL Server | Microsoft Learn
February 28, 2023 - To add namespaces to the XML constructed by the FOR XML query, first specify the namespace prefix to URI mappings by using the WITH NAMESPACES clause. Then, use the namespace prefixes in specifying the names in the query as shown in the following ...
Discussions

sql server - Parsing Typed XML with a default namespace and inconsistent root node - Database Administrators Stack Exchange
Similarly, object Quote can contain ... on SQL Server 2012. Basic problem is that every time I'm building query to extract even one value (i.e. Transaction) only thing except errors I get is empty string. ... I would like to have an example how to insert this xml into table (where every possible element has it's own column. I assume that problem is with namespaces and strong ... More on dba.stackexchange.com
🌐 dba.stackexchange.com
Select XML node with namespaces – SQLServerCentral Forums
Select XML node with namespaces Forum – Learn more on SQLServerCentral More on sqlservercentral.com
🌐 sqlservercentral.com
February 15, 2012
SQL Server Xml query with multiple namespaces - Stack Overflow
I have a table in SQL server that contains an Xml column and I am having trouble querying it. I don't know enough about XPath to determine if my query is wrong, or if it is because of what seems like More on stackoverflow.com
🌐 stackoverflow.com
xquery sql - Parsing XML with namespaces in SQL Server - Stack Overflow
I am having a hard time trying to parse an XML that has some namespaces defined: More on stackoverflow.com
🌐 stackoverflow.com
🌐
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › xml › with-xmlnamespaces
WITH XMLNAMESPACES (Transact-SQL) - SQL Server | Microsoft Learn
It is implicitly added by the XML/XPATH processor if not specified and xpath expressions can use the xsi prefix as long as the 'http://www.w3.org/2001/XMLSchema-instance' schema is properly declared in the xml document.
Top answer
1 of 2
10

The three problems with your XQuery that I can see are (and none of this has anything to do with it being Typed XML):

  1. You are not specifying the correct path to the /root node. It should be:

    SELECT t.c.value('(.)[1]','varchar(50)') as type
    from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/transaction') as t(c)
    
  2. XML is case-sensitive so you need to use an upper-case "T" for the "Transaction" node:

    SELECT t.c.value('(.)[1]','varchar(50)') as type
    from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/Transaction') as t(c)
    
  3. While those fixes would probably get you 1 value back (in this case, it should be "SaleOnly"), it won't iterate through the "item" nodes because you were too specific in the path supplied to the .nodes() function. Instead, the final node of that specification should be "item", which is what you want to iterate through. And in that case, you move the "Transaction" part up to the .value() function:

    SELECT t.c.value('(./Transaction)[1]','varchar(50)') as type
    from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item') as t(c)
    

Regarding the statement of:

I work on xml both missing and containing second xmlns declaration in root element, as well as with structure trimmed down to just root.

The "structure trimmed down to just root" should be handleable by removing everything between the first / and the / right before /root... (i.e. the WebService/NewLeads/OutputSchema). So the resulting path would be:

from @ixml.nodes('//root/item') as t(c)

NOTE:
I am not able to get this to work 100% with the namespace declared in the <WebService> element (please see additional notes as this is no longer the case). Taking that out makes it work. Giving it a prefix, such as xmlns:something="http://www.orbis-software.com/WebSvcCon" makes it work, but then that needs to be declared in the methods. The only way I can get it to work right now is by declaring the default namespace in each XML function (.nodes and .value) as follows:

SELECT t.c.value('declare default element namespace "http://www.orbis-software.com/WebSvcCon";
                  (./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('declare default element namespace "http://www.orbis-software.com/WebSvcCon";
                  /WebService/NewLeads/OutputSchema/root/item') as t(c)

NOTE 2:
Even better, you can use WITH XMLNAMESPACES to declare one or more namespaces to use for the entire query, so no need to define in each XML function. The following both work:

;WITH XMLNAMESPACES (DEFAULT 'http://www.orbis-software.com/WebSvcCon')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item') as t(c)


;WITH XMLNAMESPACES (DEFAULT 'http://www.orbis-software.com/WebSvcCon')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('//root/item') as t(c)

However, just keep in mind that if the document is missing the <WebService xmlns="http://www.orbis-software.com/WebSvcCon"> element and hence has no default namespace, then you need to remove the ;WITH XMLNAMESPACES part. Of course, if the <root> element has its own default namespace, then maybe you will need to keep it. You can play around with it until it works, now that you know the connection between these pieces.

NOTE 3:
If you do end up having two default namespaces declared -- one in the <WebService> element and one in the <root> element -- then you need to specify the URI noted in <root xmlns="bob"> and the // syntax instead of the fully-qualified path. So if your XML looked like:

<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
 <NewLeads>
  <OutputSchema>
  <root xmlns="http://someplace" type="array">

You would then use:

;WITH XMLNAMESPACES (DEFAULT 'http://someplace')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('//root/item') as t(c)

But that won't help if you do have the <WebService> element and yet the <root> element is missing the xmlns declaration. In that case you still need to specify the namespace noted in the <WebService> element. Fun, fun, fun :-).

NOTE 4:
Even more better: incorporating something mentioned in @wBob's answer, we can actually get rid of the ;WITH XMLNAMESPACES clause, and instead use a namespace wildcard. You just need to prefix every node of every XML function with *:. Now the query should look as follows:

SELECT t.c.value('(./*:Transaction)[1]','varchar(50)') AS [type],
       t.c.value('(./*:SaleProperty/*:PostDistrict)[1]','varchar(50)') AS [PostDistrict]
FROM  @ixml.nodes('//*:root/*:item') t(c);

Doing this means that the query works in all of your scenarios:

  1. Full structure starting with "WebService" node, second xmlns declaration:

    <WebService xmlns="http://www.orbis-software.com/WebSvcCon">
      <NewLeads>
        <OutputSchema>
          <root xmlns="uri" type="array">
    
  2. Full structure starting with "WebService" node, single xmlns declaration:

    <WebService xmlns="http://www.orbis-software.com/WebSvcCon">
      <NewLeads>
        <OutputSchema>
          <root type="array">
    
  3. Trimmed down structure starting with "root" node, single xmlns declaration:

    <root xmlns="uri" type="array">
    
2 of 2
5

If you just want to dump out all the element values as rows irrespective of the namespace, then you can use a namespace wildcard, eg:

DECLARE @xml XML = '<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
    <NewLeads>
        <OutputSchema>
            <root xmlns="" type="array">
                <item type="object">
                    <SaleProperty type="object">
                        <Type type="string">Freehold</Type>
                        <PostDistrict type="string">xxx</PostDistrict>
                        <Address type="string">address</Address>
                        <Value type="number">17.0</Value>
                    </SaleProperty>
                    <Transaction type="string">SaleOnly</Transaction>
                    <Quote type="object">
                        <Sale type="object">
                            <Fees type="number">450.0</Fees>
                            <Disbursements type="number">0.0</Disbursements>
                            <StampDuty type="number">0.0</StampDuty>
                            <Total type="number">450.0</Total>
                        </Sale>
                        <Discount type="number">0.0</Discount>
                        <VAT type="number">90.0</VAT>
                        <Total type="number">540.0</Total>
                    </Quote>
                    <MoverId type="number">12345678</MoverId>
                    <Name type="string">Mr AS</Name>
                    <Email type="string">[email protected]</Email>
                    <Telephone type="string">0123456789</Telephone>
                    <Comments type="string">Joint ownership</Comments>
                    <EstimatedMoveDate type="string">2015-11-25T05:57:00</EstimatedMoveDate>
                    <Charge type="number">4.99</Charge>
                    <ChargeStatus type="string">Chargeable</ChargeStatus>
                </item>
            </root>
        </OutputSchema>
    </NewLeads>
</WebService>'


-- Show all elements and their values irrespective of namespace
SELECT 
    x.y.value('local-name(..)', 'VARCHAR(MAX)') parentElementName,
    x.y.value('local-name(.)', 'VARCHAR(MAX)') elementName,
    x.y.value('.', 'VARCHAR(MAX)') elementValue
FROM @xml.nodes('//*[not(*)]') AS x(y)


SELECT 
    ws.c.value('local-name(..)', 'VARCHAR(MAX)') parentElementName,
    ws.c.value('local-name(.)', 'VARCHAR(MAX)') elementName,
    ws.c.value('.', 'VARCHAR(MAX)') elementValue
FROM @xml.nodes('*:WebService/*:NewLeads/*:OutputSchema/*:root/*:item/*[not(*)]') AS ws(c)

Result:

You could then do something like dynamic pivot if you need to turn this into columns. If you do want to specify elements explicitly you can use a similar technique.

Judging from your question you probably need to spend more time learning about XML namespaces, so start here:

Adding Namespaces Using WITH XMLNAMESPACES http://msdn.microsoft.com/en-us/library/ms177400.aspx

🌐
C# Corner
c-sharpcorner.com › blogs › fetch-data-from-xml-with-namespace-in-sql-server1
Fetch Data From XML With Namespace in SQL Server
December 9, 2015 - SQL Server also manage XML data efficiently to fetch data from XML content. Here we will discuss how to fetch data when XML contains Namespace tag. Let’s start: Using Code The following code snippet defines a XML variable with namespace tag (xmlns) containing “http://tempnamespace/default”, ... @data.value('(EmplyoeeInfo/Employee/Profession)[1]', 'nvarchar(100)') as Profession; In proceeding code it uses with XML Namespaces which provides namespace URI support in the following ways:
🌐
SQLServerCentral
sqlservercentral.com › forums › topic › select-xml-node-with-namespaces
Select XML node with namespaces – SQLServerCentral Forums
February 15, 2012 - r.r.value('declare namespace x="http://schemas.microsoft.com/BizTalk/EDI/X12/2006"; x:BHT_BeginningofHierarchicalTransaction[2]/BHT06_ClaimorEncounterIdentifier[1]', 'varchar(300)'), r.r.value('SE[1]/SE01_TransactionSegmentCount[1]','int'), r.r.value('SE[1]/SE02_TransactionSetControlNumber[1]','int') FROM @xml.nodes('declare namespace xx="http://schemas.microsoft.com/BizTalk/EDI/X12/2006"; /xx:X12_00501_837_P') r(r)
🌐
Notes on SQL
sqlrambling.net › 2017 › 12 › 19 › xmlnamespaces-basic-example
XMLNAMESPACES – Basic Example | Notes on SQL
December 20, 2017 - WITH XMLNAMESPACES('CustomerData' AS c, 'OrderData' AS o, 'ProductData' AS p) SELECT [c:Customer].Name , [c:Customer].Address, [o:Order].Address , [o:Order].OrderDate , [o:Order].Updated , [p:Product].ProductCode , [p:Product].Name , [p:Product].Updated FROM dbo.Customers AS [c:Customer] INNER JOIN dbo.Orders AS [o:Order] ON [o:Order].OrderID = [c:Customer].OrderID INNER JOIN dbo.Products AS [p:Product] ON [p:Product].ProductID = [o:Order].ProductID FOR XML AUTO, ELEMENTS, ROOT('CustomerOrders'); In the example above, “CustomerData” is a Uniform Resource Identifier (URI) and the associated prefix is ‘c’. “OrderData” is another URI with a prefix of ‘o’ and “ProductData” is the third URI with an associated prefix of ‘p’. The URI is used to uniquely identify a namespace.
Find elsewhere
🌐
SQLTeam
forums.sqlteam.com › t › xml-file-namespace-query › 21215
XML File Namespace Query - sql2014 - SQLTeam.com Forums
June 13, 2022 - Hi everyone, I have generated a XML file of a database in SQL which is printing some not wanted data. How can I skip this data being generated with the file? I just want a simple file with normal nametags and if null value is there then just print that tag.... Please see screenshot:
Top answer
1 of 1
1

Please check the T-SQL below.

Your XML has just two namespaces, not three. I adjusted the .nodes() method XPath expression. Columns that have NULL values have questionable XPath in the .value() method.

SQL

DECLARE @xmlDoc XML = 
 '<ns0:FlightReservations xmlns:ns0="http://SchemaLesson.FlightReservations">
  <ns1:FlightReservation xmlns:ns1="http://SchemaLesson.FlightReservation">
    <DepartureAirportCode>DFW</DepartureAirportCode>"
  </ns1:FlightReservation>
  <ns1:FlightReservation xmlns:ns1="http://SchemaLesson.FlightReservation">
    <DepartureAirportCode>OKC</DepartureAirportCode>"
  </ns1:FlightReservation>
  </ns0:FlightReservations>';

WITH XMLNAMESPACES ('http://SchemaLesson.FlightReservations' as ns0
    , 'http://SchemaLesson.FlightReservation' as ns1)
select c.query('.') AS result  , 
    c.value('(.//*[local-name()="DepartureAirportCode"])[1]','varchar(5)') AS DepartureAirportCode1, 
    c.value('(DepartureAirportCode/text())[1]', 'varchar(5)') AS DepartureAirportCode2, 
    c.value('(.//ns1:FlightReservation/DepartureAirportCode)[1]', 'varchar(5)') AS DepartureAirportCode3, 
    c.value('(.//DepartureAirportCode)[1]', 'varchar(5)') AS DepartureAirportCode4, 
    c.value('(.//ns1:DepartureAirportCode)[1]', 'varchar(5)') AS DepartureAirportCode5 
from @xmlDoc.nodes('/ns0:FlightReservations/ns1:FlightReservation')  AS t(c);

Output

+----------------------------------------------------------------------------------------------------------------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
|                                                           result                                                           | DepartureAirportCode1 | DepartureAirportCode2 | DepartureAirportCode3 | DepartureAirportCode4 | DepartureAirportCode5 |
+----------------------------------------------------------------------------------------------------------------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
| <ns1:FlightReservation xmlns:ns1="http://SchemaLesson.FlightReservation"><DepartureAirportCode>DFW</DepartureAirportCode>" |                       |                       |                       |                       |                       |
|   </ns1:FlightReservation>                                                                                                 | DFW                   | DFW                   | NULL                  | DFW                   | NULL                  |
| <ns1:FlightReservation xmlns:ns1="http://SchemaLesson.FlightReservation"><DepartureAirportCode>OKC</DepartureAirportCode>" |                       |                       |                       |                       |                       |
|   </ns1:FlightReservation>                                                                                                 | OKC                   | OKC                   | NULL                  | OKC                   | NULL                  |
+----------------------------------------------------------------------------------------------------------------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
🌐
GitHub
github.com › MicrosoftDocs › sql-docs › blob › live › docs › relational-databases › xml › add-namespaces-to-queries-with-with-xmlnamespaces.md
sql-docs/docs/relational-databases/xml/add-namespaces-to-queries-with-with-xmlnamespaces.md at live · MicrosoftDocs/sql-docs
To add namespaces to the XML constructed by the FOR XML query, first specify the namespace prefix to URI mappings by using the WITH NAMESPACES clause. Then, use the namespace prefixes in specifying the names in the query as shown in the following ...
Author   MicrosoftDocs
🌐
Narkive
microsoft.public.sqlserver.xml.narkive.com › tQHYi85Y › use-sql-variable-to-declare-xml-namespaces-in-stored-procedures
use sql:variable to declare xml namespaces in stored procedures
Permalink Somewhat tedious, and possibly slow, way to paramaterize a namespace in in a T-SQL variable for an XQuery query if you must: DECLARE @x XML; SET @x = '<a xmlns="microsoft"><b/></a>'; DECLARE @ns NVARCHAR(MAX); SET @ns = 'microsoft' select @x.query(' (/*[local-name()="a" and namespace-uri()=sql:variable("@ns")] /*[local-name()="b" and namespace-uri()=sql:variable("@ns")]) ') Unfortunately SQL Server does not support let, it it did this would take less typing: DECLARE @x XML; SET @x = '<a xmlns="microsoft"><b/></a>'; DECLARE @ns NVARCHAR(MAX); SET @ns = 'microsoft' select @x.query( let $ns := sql:variable("@ns") return /*[local-name()="a" and namespace-uri()=$ns] /*[local-name()="b" and namespace-uri()=$ns] ) Dan Hello sql, Post by sql developer select @validXml.query(' .') basically I would like to parameterized the value of the default namespace in the xquery.
🌐
SQLServerCentral
sqlservercentral.com › forums › topic › parse-xml-with-multiple-namespaces
Parse XML with multiple namespaces – SQLServerCentral Forums
April 5, 2016 - I am trying to write a sql query to extract the Success value and the ConnectionError value. Any help with this would be greatly appreciated · You can simply wildcard all the namespaces, this samples uses default for the highest level and wildcards the rest.
🌐
GitHub
github.com › MicrosoftDocs › sql-docs › blob › live › docs › t-sql › xml › with-xmlnamespaces.md
sql-docs/docs/t-sql/xml/with-xmlnamespaces.md at live · MicrosoftDocs/sql-docs
xml_namespace_uri A Uniform Resource Identifier (URI) that identifies the XML namespace that is being declared. xml_namespace_uri is a SQL string. xml_namespace_prefix Specifies a prefix to be mapped and associated with the namespace URI value specified in xml_namespace_uri.
Author   MicrosoftDocs
🌐
Benjamin's Blog
sqlbenjamin.wordpress.com › 2019 › 04 › 26 › sql-tip-xml-in-sql
SQL Tip: XML in SQL - Benjamin's Blog - WordPress.com
February 16, 2022 - Now, if I were to want to write a query against this XML to pull out the “Language” (the highlighted line above) I could do that by using the “value” method and providing the XQuery required for the method to get this data. As you’ll see from the example query below though I wrote the XQuery to do so twice – once with a namespace declaration (for typed XML) and once with no namespace declaration (for untyped XML).
Top answer
1 of 2
3

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.

2 of 2
1

Declare your namespace again when using xquery for xml with namespaces.

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)

select @x.query('declare default element namespace "http://schemas.xmlsoap.org/soap/envelope/";   
    /Envelope')
🌐
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › xml › value-method-xml-data-type
value() method (xml data type) - SQL Server | Microsoft Learn
This means that it has a schema collection associated with it. In the Modules and Prologs - XQuery Prolog, the namespace declaration is used to define the prefix that is used later in the query body.