XML Parsing problem

Question

Post reply

XML Parsing problem

Simon G

Ten Centuries

Points: 1285
More actions
May 1, 2009 at 2:52 am

#200067

Hi guys,
I have the following xml format in an xml field in my db.
[nodes]
[nodes]
[nodes]
I've done this many a time before but this structure is slightly different and my code isn't working!?
How do I extract the Post Advert nodes?
I'm using this at the moment:
SELECT node.l.value('field[1]','nvarchar(20)') as fieldname
....
FROM my_xml_Data CROSS APPLY xml_data_column.nodes('/MyPostAd/Ad/MyPostAd') node(l)
I've tried a few ways to no avail 🙁

Viewing 9 posts - 1 through 8 (of 8 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Hi,

I assume you're missing the namespace declaration.

;WITH XMLNAMESPACES (

DEFAULT ''

)

SELECT node.l.value('text()[1]','nvarchar(20)') as fieldname

FROM my_xml_Data cross apply xml_data_column.nodes('/MyPostAd/Ad/MyPostAd') node(l)

/*

result:

fieldname

---------

[node]

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Simon G Ten Centuries Points: 1285 More actions · Answer 2

that still doesn't seem to be working. I added the namespace but nothing comes back 🙁

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 3

XML is case sensitive so

FROM my_xml_Data CROSS APPLY xml_data_column.nodes('/MyPostAd/Ad/MyPostAd') node(l)

should probably be

FROM my_xml_Data CROSS APPLY xml_data_column.nodes('/MYPOSTAD/AD/MYPOSTAD') node(l)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Simon G Ten Centuries Points: 1285 More actions · Answer 4

Hi guys,

The case is correct. I've double checked and it's all ok.

I'm wondering if I have set the namespace wrong...

Just to clarify with a bit more detail. Here is my XML and SQL.

XML

nodes....

nodes...

(the case is correct on this...the forum alters it to upper case for some reason!?)

and my SQL is:

;WITH XMLNAMESPACES (

DEFAULT 'http://mywebsite.com/msg/'

)

SELECT node.l.value('Ref[1]','NVARCHAR(50)') AS MyRef

FROM my_xml_Data CROSS APPLY xml_data_column.nodes('/MyPostAd/Ad/MyPostAd') node(l)

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 5

Try this

WITH XMLNAMESPACES (

'http://mywebsite.com/msg/' AS n1, 'http://mywebsite.com/' AS n2

)

SELECT node.l.value('Ref[1]','NVARCHAR(50)') AS MyRef

FROM my_xml_Data CROSS APPLY xml_data_column.nodes('/n1:MyPostAd/n2:Ad/n2:MyPostAd') node(l)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Simon G Ten Centuries Points: 1285 More actions · Answer 6

Hi Mark,

Ok...that's getting a bit further I think. I've done what you say but I now get the correct number of rows, but all NULL values.

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 7

Also change

SELECT node.l.value('Ref[1]','NVARCHAR(50)') AS MyRef

to

SELECT node.l.value('n2:Ref[1]','NVARCHAR(50)') AS MyRef

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Simon G Ten Centuries Points: 1285 More actions · Answer 8

Ah I see. I'm with you now.

Fantastic, that worked. Thanks Mark! It's just the 1st time I've encountered namespaces on my XML imports