Help with XQUERY

Question

Post reply

Help with XQUERY

Jonathan Mallia

SSCertifiable

Points: 5192
More actions
November 29, 2012 at 7:29 am

#266811

Hi all,
I have the following XML stroed in a table colum.
I need to see all the following XML information in two rows (2 Row nodes).
<comments_data>
<Row>
<id>204506806280354_433001873430845_1108251</id>
<from>
<name>Francis Champs</name>
<id>100003216904024</id>
</from>
<message>Please help :)</message>
<message_tags>
<Row>
<id>64547938</id>
<name>John</name>
<type>user</type>
<offset>7</offset>
<length>14</length>
</Row>
</message_tags>
<created_time>2012-01-21T09:20:29+0000</created_time>
<likes>1</likes>
</Row>
<Row>
<id>204506806280354_433001873430845_1108329</id>
<from>
<name>Joseph</name>
<id>6473938</id>
</from>
<message>Thanks for the time :-D</message>
<created_time>2012-11-21T11:09:44+0000</created_time>
</Row>
</comments_data>
I used the partial query but did not return the desired results...
SELECT DISTINCT
T.ID AS PostID,
S.N.value('name[1]', 'varchar(150)') AS UserName,
S.N.value('id[1]', 'varchar(150)') AS UserID
FROM
(SELECT ID, CAST(CommentsData AS XML) CommentsData
FROM tb_Posts) AS T
CROSS APPLY T.CommentsData.nodes('/comments_data/Row/id/from') AS S(N)
Can anyone assist please?
Much appreciated,
Jon

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

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

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 1

Looks like a typo

CROSS APPLY T.CommentsData.nodes('/comments_data/Row/id/from') AS S(N)

should be

CROSS APPLY T.CommentsData.nodes('/comments_data/Row/from') AS S(N)

____________________________________________________

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

I guess the main problem is in xQuery, you shouldn't have "id " in it:

nodes('/comments_data/Row/from') AS S(N)

Check this:

declare @CommentsData xml =

'<comments_data>

<Row>

<id>204506806280354_433001873430845_1108251</id>

<from>

<name>Francis Champs</name>

<id>100003216904024</id>

</from>

<message>Please help :)</message>

<message_tags>

<Row>

<id>64547938</id>

<name>John</name>

<type>user</type>

<offset>7</offset>

<length>14</length>

</Row>

</message_tags>

<created_time>2012-01-21T09:20:29+0000</created_time>

<likes>1</likes>

</Row>

<Row>

<id>204506806280354_433001873430845_1108329</id>

<from>

<name>Joseph</name>

<id>6473938</id>

</from>

<message>Thanks for the time </message>

<created_time>2012-11-21T11:09:44+0000</created_time>

</Row>

</comments_data>

'

SELECT DISTINCT

S.N.value('name[1]', 'varchar(150)') AS UserName,

S.N.value('id[1]', 'varchar(150)') AS UserID

FROM @CommentsData.nodes('/comments_data/Row/from') AS S(N)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jonathan Mallia SSCertifiable Points: 5192 More actions · Answer 3

Actually yes I had a typo :s

I have created the following XQUERY and seems to work well.. Any further suggestions maybe I can enhance the query?

SELECT DISTINCT

T.ID AS PostID,

D.N.value('name[1]', 'varchar(150)') AS UserName,

D.N.value('id[1]', 'varchar(150)') AS UserID,

C.N.value('.[1]', 'varchar(150)') AS [Message],

E.N.value('.[1]', 'varchar(150)') AS CreatedTime,

F.N.value('.[1]', 'varchar(150)') AS Likes

FROM

(SELECT ID, CAST(CommentsData AS XML) CommentsData

FROM tb_Posts) AS T

CROSS APPLY T.CommentsData.nodes('/comments_data/Row') AS S(N)

OUTER APPLY S.N.nodes('message') AS C(N)

OUTER APPLY S.N.nodes('from') AS D(N)

OUTER APPLY S.N.nodes('created_time') AS E(N)

OUTER APPLY S.N.nodes('likes') AS F(N)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

You don't need to OUTER APPLY, just use relative xPath:

SELECT DISTINCT

S.N.value('name[1]', 'varchar(150)') AS UserName,

S.N.value('id[1]', 'varchar(150)') AS UserID,

S.N.value('../message[1]', 'varchar(150)') AS [Message],

S.N.value('../from[1]', 'varchar(150)') AS [From],

S.N.value('../created_time[1]', 'varchar(150)') AS [CreatedTime],

S.N.value('../likes[1]', 'varchar(150)') AS [Likes]

FROM ...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jonathan Mallia SSCertifiable Points: 5192 More actions · Answer 5

Jonathan Mallia

SSCertifiable

Points: 5192

November 29, 2012 at 8:01 am

#1563798

Excellent Eugene Elutin,

Thanks very much!

Jonathan Mallia SSCertifiable Points: 5192 More actions · Answer 6

Hi guys,

I am getting the error message:

XML parsing: line 7, character 411, illegal name character

just because I have characters like &, <, > in the data of the XML values.

Are there any solutions to this? Maybe something similar to UrlEscape for link?

I tried

SELECT ID, CAST( REPLACE(CommentsData, '>', '') AS XML) CommentsData

FROM tb_Posts

but of course this deform the resultant XML

Cheers,

Jon

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

& (and some other) characters in XML needs to be escaped (eg. & for &).

Otherwise, any XML parser will reject it as not well-formed XML...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jonathan Mallia SSCertifiable Points: 5192 More actions · Answer 8

Hi Eugene,

How can I escape the special characters (<, >, &) in XQUERY and successfully parse the XML ? The unfortunate thing is that I cannot ask the source to forward the XML without these special characters.

Thanks

Jon

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

Jonathan Mallia (11/30/2012)
Hi Eugene,
How can I escape the special characters (<, >, &) in XQUERY and successfully parse the XML ? The unfortunate thing is that I cannot ask the source to forward the XML without these special characters.
Thanks
Jon

From computer perspective, if your text contains un-escapped xml special characters, this text is not XML! Try this:

declare @xmldata1 xml = '<mydata>Bla Bla Bla</mydata>'

and then this:

declare @xmldata2 xml = '<mydata>Bla & Bla Bla</mydata>'

You can see that the second one failed to assign the text to a variable of XML type.

Now, you may have a real problem, as the only way to fix it is in a process which inserts data into your table! You cannot use REPLACE in sql to escape "<" and ">". It will destroy xml tags.

So, change the source table column definition to XML (instead of text/varchar) and make sure when something is inserted there is real XML, not just XML looking text.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]