Parse XML with multiple namespaces

Question

Parse XML with multiple namespaces

CH-328334

SSC Veteran

Points: 252
More actions
April 5, 2016 at 9:12 am

#312915

Hi,
I have an xml file ,which contains multiple namespaces and are having difficulty shredding it to obtain the values.
Here is the xml
<CommonApiResponseOfSuccessObjDataMR1gN5bk xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/ProductionModels">
<Data xmlns:d2p1="http://schemas.datacontract.org/2004/07/ProductionModels.Shared">
<d2p1:SuccessObjData>
<d2p1:Success>true</d2p1:Success>
</d2p1:SuccessObjData>
</Data>
<Status>
<ConnectionCode>200</ConnectionCode>
<ConnectionError>false</ConnectionError>
</Status>
</CommonApiResponseOfSuccessObjDataMR1gN5bk>
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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

CH-328334 (4/5/2016)
Hi,
I have an xml file ,which contains multiple namespaces and are having difficulty shredding it to obtain the values.
Here is the xml
<CommonApiResponseOfSuccessObjDataMR1gN5bk xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/ProductionModels">
<Data xmlns:d2p1="http://schemas.datacontract.org/2004/07/ProductionModels.Shared">
<d2p1:SuccessObjData>
<d2p1:Success>true</d2p1:Success>
</d2p1:SuccessObjData>
</Data>
<Status>
<ConnectionCode>200</ConnectionCode>
<ConnectionError>false</ConnectionError>
</Status>
</CommonApiResponseOfSuccessObjDataMR1gN5bk>
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.

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TXML XML = '<CommonApiResponseOfSuccessObjDataMR1gN5bk xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/ProductionModels">

<Data xmlns:d2p1="http://schemas.datacontract.org/2004/07/ProductionModels.Shared">

<d2p1:SuccessObjData>

<d2p1:Success>true</d2p1:Success>

</d2p1:SuccessObjData>

</Data>

<Status>

<ConnectionCode>200</ConnectionCode>

<ConnectionError>false</ConnectionError>

</Status>

</CommonApiResponseOfSuccessObjDataMR1gN5bk>';

;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS XM1

,DEFAULT 'http://schemas.datacontract.org/2004/07/ProductionModels'

)

SELECT

SOD.DATA.value( '(*:Success/text())[1]' ,'VARCHAR(50)') AS Success

,STAT.DATA.value('(*:ConnectionCode/text())[1]' ,'VARCHAR(50)') AS ConnectionCode

,STAT.DATA.value('(*:ConnectionError/text())[1]','VARCHAR(50)') AS ConnectionError

FROM @TXML.nodes('CommonApiResponseOfSuccessObjDataMR1gN5bk') AS ROOT(DATA)

CROSS APPLY ROOT.DATA.nodes('*:Data/*:SuccessObjData') AS SOD(DATA)

CROSS APPLY ROOT.DATA.nodes('*:Status') AS STAT(DATA);

Output

Success ConnectionCode ConnectionError

-------- --------------- ----------------

true 200 false

CH-328334 SSC Veteran Points: 252 More actions · Answer 2

CH-328334

SSC Veteran

Points: 252

April 5, 2016 at 9:48 am

#1869868

Perfect, Thanks so much

R.P.Rozema SSChampion Points: 12317 More actions · Answer 3

But if you want to do it exact, for example because the same element name exist in multiple schemas. Here's the query to get those values:

with xmlnamespaces (

DEFAULT 'http://schemas.datacontract.org/2004/07/ProductionModels',

'http://www.w3.org/2001/XMLSchema-instance' AS xsi,

'http://schemas.datacontract.org/2004/07/ProductionModels.Shared' as p1

)

select

r.d.value('Data[1]/p1:SuccessObjData[1]/p1:Success[1]','bit') as Success,

r.d.value('Status[1]/ConnectionError[1]','bit') as ConnectionError

from @xml.nodes('/CommonApiResponseOfSuccessObjDataMR1gN5bk[1]') r(d)

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Totally off subject...

I'm still amazed when I see something like this. 472 bytes of I/O for the XML to get 52 bytes of info, including the header for the info. Each additional row of similar data would take an additional 130 bytes per 14 byte row. Put in real terms, a 1GB file of simple row data would take more than 9GB of XML data to transmit or store both in memory and on disk. If you translate it into CPU time to handle it, not including the shredding it would need to be usable, that's like sending a 3GHz CPU back in time to 333MHz first available in the late '90's.

On the bright side, it's really kept performance and throughput on the frontal lobes of the folks that build hardware, although I'm not sure which came first... the need or the ability.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)