XML query on SQL Server

Question

I'm very new to XML query and having trouble with the query below. Can anyone help me, please?

I am not getting any data from the query below. But somehow I figured that that removing the row xmlns="http://www.w3.org/1999/xhtml from XML data works fine.

However, the original XML data contains this row. Hence, not sure how to write correct SQL.

create table xmltest (col xml null)
insert into xmltest values('
<xml xmlns="http://www.w3.org/1999/xhtml" xmlns:iso4217="http://www.xbrl.org/2003/iso4217" xmlns:ix="http://www.xbrl.org/2008/inlineXBRL" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="-//XBRL International//DTD XHTML Inline XBRL 1.0//EN">
    <ix:nonnumeric contextref="CurrentAccumulatedQ3Instant" name="tse-ed-t:DocumentName">aaa</ix:nonnumeric>
    <ix:nonnumeric contextref="CurrentAccumulatedQ3Instant" format="ixt:dateerayearmonthdayjp" name="tse-ed-t:FilingDate">bbb</ix:nonnumeric>
    <ix:nonnumeric contextref="CurrentAccumulatedQ3Instant" name="tse-ed-t:CompanyName">ccc</ix:nonnumeric>
    <ix:nonnumeric contextref="CurrentAccumulatedQ3Instant" format="ixt:booleantrue" name="tse-ed-t:TokyoStockExchange">ddd</ix:nonnumeric>
    <ix:nonnumeric contextref="CurrentAccumulatedQ3Instant" format="ixt:booleanfalse" name="tse-ed-t:StockExchange" />
</xml>
')
select 
T.Col.value('fn:local-name(.)','nvarchar(max)'),
T.Col.value('.','nvarchar(max)')
from xmltest
cross apply col.nodes('
declare namespace aaa="http://www.w3.org/1999/xhtml";
declare namespace ix="http://www.xbrl.org/2008/inlineXBRL";
//xml/ix:*'
) as T(Col)

Gottfried Lesigang · Accepted Answer · 2018-06-26 08:06:06Z

Your code is pretty close.

You are declaring the used namespaces. But you forgot to use it. Add the aaa: to the xml and it will return values:

select 
T.Col.value('fn:local-name(.)','nvarchar(max)'),
T.Col.value('.','nvarchar(max)')
from xmltest
cross apply col.nodes('
declare namespace aaa="http://www.w3.org/1999/xhtml";
declare namespace ix="http://www.xbrl.org/2008/inlineXBRL";
//aaa:xml/ix:*'
) as T(Col)

Btw: Don't use the deep search // when you don't need it, one / is correct here.

I'd suggest do change this like here:

;WITH XMLNAMESPACES(DEFAULT 'http://www.w3.org/1999/xhtml'
                           ,'http://www.xbrl.org/2008/inlineXBRL' AS ix)
select 
T.Col.value('fn:local-name(.)','nvarchar(max)'),
T.Col.value('.','nvarchar(max)')
from xmltest
cross apply col.nodes('/xml/ix:*') as T(Col)

This will define all namespaces in advance. And you can omit the default namespace (just as the original does).

Collectives™ on Stack Overflow

XML query on SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related