Anomaly in OPENXML results (crippling my code)

Question

Anomaly in OPENXML results (crippling my code)

BobA 66314

Valued Member

Points: 67
More actions
April 26, 2010 at 1:08 pm

#96658

declare @IncidentXML xml = '<Root ReadOnly="false">
<Incident>
<RowId>44021</RowId>
<FileID>-32768</FileID><IncidentID>41894</IncidentID><Action>D</Action><ClearanceCode>Q</ClearanceCode>
</Incident></Root>';
declare @XMLHandleint; exec sp_xml_preparedocument @XMLHandle OUTPUT, @IncidentXML;
select FileID, IncidentID, RowId
, Action, ClearanceCode
from openXML (@XMLHandle, '/Root/Incident/.', 2)
with (
FileIDsmallint, IncidentID int
,RowIDint,Actionchar(1)
,ClearanceCodechar(1),ClearanceDatedate)
Results:
FileID IncidentID RowIdAction ClearanceCode
-32768 41894 NULL D Q:w00t:

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

It seems like you're still using OPENXML instead of XQuery even though you're already on SS2K8, obviously (guessing based on the syntax and the DATE data type...).

When using XQuery it would look like

SELECT

c.value('FileID[1]','int') AS FileID,

c.value('IncidentID[1]','int') AS IncidentID,

c.value('RowId[1]','int') AS RowId,

c.value('Action[1]','char(1)') AS ACTION,

c.value('ClearanceCode[1]','char(1)') AS ClearanceCode

FROM @IncidentXML.nodes('/Root/Incident') T(c)

/* result set

FileIDIncidentIDRowIdActionClearanceCode

-327684189444021DQ

*/

Side note: good job to provide everything we need (e.g. source data, query and expected result)! Made it easy to work on. 🙂

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]

BobA 66314 Valued Member Points: 67 More actions · Answer 2

Thanks. XQuery does the work!

I use OPENXML because it is faster, and the task occurs while online user waits. Any idea why it ignores this one element?

BobA

LutzM SSC Guru Points: 107049 More actions · Answer 3

BobA 66314 (4/26/2010)
Thanks. XQuery does the work!
I use OPENXML because it is faster, and the task occurs while online user waits. Any idea why it ignores this one element?
BobA

Any example available to support that statement? Usually this impression is caused by a "semi-optimal" XQuery...

Regarding your question: You have RowID defined in your openXML but the xml has RowId. Looks like an issue of case sensitivity.

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]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

lmu92 (4/26/2010)
BobA 66314 (4/26/2010)
Thanks. XQuery does the work!
I use OPENXML because it is faster, and the task occurs while online user waits. Any idea why it ignores this one element?
BobA
Any example available to support that statement? Usually this impression is caused by a "semi-optimal" XQuery...
Regarding your question: You have RowID defined in your openXML but the xml has RowId. Looks like an issue of case sensitivity.

I didn't even notice that at first, Lutz. Another reason for ensuring consistancy when coding.

LutzM SSC Guru Points: 107049 More actions · Answer 5

Oh, I forgot: if the process is performance crucial, you should think about shredding the xml into properly indexed relational tables... 😀

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]

LutzM SSC Guru Points: 107049 More actions · Answer 6

Lynn Pettis (4/26/2010)
... Another reason for ensuring consistancy when coding.

I guess as long as we're not forced by db collations we're getting lax "by nature"...

At least I do. :blush:

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]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

lmu92 (4/26/2010)
Lynn Pettis (4/26/2010)
... Another reason for ensuring consistancy when coding.
I guess as long as we're not forced by db collations we're getting lax "by nature"...
At least I do. :blush:

Takes effort. Having systems that use both case sensitive and case insensitive collations I have to work at it for multi-server code, so it becomes more second nature. I know, however, that I too slip at times. 😉