Anomaly in OPENXML results (crippling my code)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » XML » Anomaly in OPENXML results (crippling my...

Anomaly in OPENXML results (crippling my code)

Rate Topic

Display Mode

Topic Options

Author

Message

BobA 66314

Posted Monday, April 26, 2010 1:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 4:00 PM
Points: 5, Visits: 70

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 @XMLHandle int; exec sp_xml_preparedocument @XMLHandle OUTPUT, @IncidentXML;

select FileID, IncidentID, RowId
, Action, ClearanceCode
from openXML (@XMLHandle, '/Root/Incident/.', 2)
with (
FileID smallint , IncidentID int
,RowID int ,Action char(1)
,ClearanceCode char(1) ,ClearanceDate date )

Results:
FileID IncidentID RowIdAction ClearanceCode
-32768 41894 NULL D Q w00t

Post #910637

LutzM

Posted Monday, April 26, 2010 1:29 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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
FileID	IncidentID	RowId	Action	ClearanceCode
-32768	41894	44021	D	Q
*/

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

Lutz
A pessimist is an optimist with experience.

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

Post #910654

BobA 66314

Posted Monday, April 26, 2010 2:03 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 4:00 PM
Points: 5, Visits: 70

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

Post #910679

LutzM

Posted Monday, April 26, 2010 2:18 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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.

Post #910693

Lynn Pettis

Posted Monday, April 26, 2010 2:22 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 21,625, Visits: 27,468

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

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

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #910699

LutzM

Posted Monday, April 26, 2010 2:22 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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

Post #910702

LutzM

Posted Monday, April 26, 2010 2:36 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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

Post #910713

Lynn Pettis

Posted Monday, April 26, 2010 2:41 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 21,625, Visits: 27,468

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

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #910718

« Prev Topic | Next Topic »

Permissions