CODEWITHSUNDEEP
sql-serverxml
satoshi
satoshi

Reputation: 23

XML query on SQL Server

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)

Upvotes: 2

Views: 68

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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).

Upvotes: 2

Related Questions