June 8, 2009 at 3:19 pm
We are currently using OPENXML within a query like:
SELECT * FROM OPENXML (@idoc, '//HeadConfig',2) WITH (
VersionInfo int,
Version2Info int,
Version3Info int)
Getting:
VersionInfo, Version2Info, Version3Info
10 12 13
And I would like to test the performance improvement (if any) using code like:
SELECT Node.value('@VersionInfo', 'int') AS VersionInfo
SELECT Node.value('@Version2Info', 'int') AS VersionInfo2
SELECT Node.value('@Version3Info', 'int') AS VersionInfo3
FROM @FileContent.nodes('//HeadConfig/*') TempXML (Node)
Getting:
VersionInfo, Version2Info, Version3Info
10 NULL NULL
12 NULL NULL
13 NULL NULL
but I've been unable to get the wanted result like the following posts suggests (the OPENXML query get what I want but the second query using Node.Value get the data into the same column instead of by rows). I believe it does come from the 2 option in the OPENXML which state element-centric mapping.
I've check the following examples for building my second query (Node.Value):
https://www.sqlservercentral.com/blogs/prosqlxml/archive/2008/01/22/xml-data-type-nodes-method.aspx
https://www.sqlservercentral.com/blogs/michael_coles/archive/tags/SQL/default.aspx
If I'm right about it, is there any way to query using Node.Value in element-centric instead of attribute-centric?
The XML look like (I've shrunk most of it):
June 9, 2009 at 12:19 am
Hi Megistal,
first of all thanx for the sample data structure you provided! Makes it easy to work on!!
This solution should work.
DECLARE @FileContent xml
SET @FileContent='101213'
SELECT Node.value('VersionInfo[1]', 'int') AS VersionInfo,
Node.value('Version2Info[1]', 'int') AS VersionInfo2,
Node.value('Version3Info[1]', 'int') AS VersionInfo3
FROM @FileContent.nodes('/file/HeadConfig') TempXML (Node)
The reason why your's didn't get the results as expected is that you're trying to query attributes (@VersionInfo), whereas the content you need is an element.
June 9, 2009 at 5:39 am
Great lmu92! Thank you!
It does work exactly how I wanted too!
Now I'll be able to benchmark it!
June 9, 2009 at 7:55 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply