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 OverflowLook 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)
If you are trying to extract data and the namespace might differ between records, you can use a wildcard for the namespace prefix. Just put "*:" before each element name in the last line of the OP's original code, like this:
FROM @xVar.nodes('/*:ReportData/*:ReportId[1]') AS reportdata(item)
Note that you need to use the wildcard at every level, not just at the same level as you see a namespace declaration in the xml. This is because namespaces are inherited by each level from the level above it.
sql server - Parsing Typed XML with a default namespace and inconsistent root node - Database Administrators Stack Exchange
Select XML node with namespaces – SQLServerCentral Forums
SQL Server Xml query with multiple namespaces - Stack Overflow
xquery sql - Parsing XML with namespaces in SQL Server - Stack Overflow
The three problems with your XQuery that I can see are (and none of this has anything to do with it being Typed XML):
You are not specifying the correct path to the
/rootnode. It should be:SELECT t.c.value('(.)[1]','varchar(50)') as type from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/transaction') as t(c)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)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:
Full structure starting with "WebService" node, second xmlns declaration:
<WebService xmlns="http://www.orbis-software.com/WebSvcCon"> <NewLeads> <OutputSchema> <root xmlns="uri" type="array">Full structure starting with "WebService" node, single xmlns declaration:
<WebService xmlns="http://www.orbis-software.com/WebSvcCon"> <NewLeads> <OutputSchema> <root type="array">Trimmed down structure starting with "root" node, single xmlns declaration:
<root xmlns="uri" type="array">
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
I know that answer is accepted, but there is actually simplier way of doing it, if the only thing you need to do is select node value. Just use * as namespace name:
SELECT MessageXml
, MessageXml.value('(/*:Envelope/*:Body/*:FetchRequest/*:Contract/*:TransactionId)[1]'
, 'varchar(max)')
FROM dbo.Message
You have two problems :
- you're not respecting the implicit default XML namespace on the
<FetchRequest>node - the XML namespace with the
a:prefix is first defined on the<s:Envelope>node, and is being re-declared on the<Contract>node (really really bad practice in my opinion) and you need to use the second declaration for anything below the<Contract>node.
So you need something like this (I prefer to define the XML namespaces upfront, in a WITH XMLNAMESPACES() statement):
;WITH XMLNAMESPACES('http://www.w3.org/2003/05/soap-envelope' AS s,
'http://www.foobar.org/2014/04/datacontracts' AS a,
'http://www.foobar.org/my/schema' AS fb)
SELECT
MessageXml,
MessageXml.value('(/s:Envelope/s:Body/fb:FetchRequest/fb:Contract/a:TransactionId)[1]', 'varchar(max)')
FROM
dbo.Message
This will output the whole query and the value ABC20140402000201 for your second column.
If your XML document has XML namespaces, then you need to consider those in your queries!
So if your XML looks like your sample, then you need:
-- define the default XML namespace to use
;WITH XMLNAMESPACES(DEFAULT 'bar')
SELECT
x.u.value('Name[1]', 'varchar(100)') as Name
from
@XMLDOC.nodes('/Feed/Product') x(u)
Or if you prefer to have explicit control over which XML namespace to use (e.g. if you have multiple), use XML namespace prefixes:
-- define the XML namespace
;WITH XMLNAMESPACES('bar' as b)
SELECT
x.u.value('b:Name[1]', 'varchar(100)') as Name
from
@XMLDOC.nodes('/b:Feed/b:Product') x(u)
As well as the XMLNAMESPACES solution, you can also use the hideously bulky local-name syntax...
DECLARE @XMLDOC XML
SET @XMLDOC = '<Feed xmlns="bar"><Product><Name>Foo</Name></Product></Feed>'
SELECT x.u.value('*[local-name() = "Name"][1]', 'varchar(100)') as Name
from @XMLDOC.nodes('/*[local-name() = "Feed"]/*[local-name() = "Product"]') x(u)
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.
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')