February 10, 2012 at 4:56 pm
Hey everyone,
I searched but can't find any examples describing how to interrogate some specific XML. I have an XML structure that is (shall we say) less than optimal, but for the sake of argument, I can't change it, I have to deal with it as it is. Below is an example of what I'm trying to do
===========================================================================
DECLARE @hdoc int,@str nvarchar(max)
set @STR = '
<root>
<item>
<var name="ITEMID">
<number>3520.0</number>
</var>
<var name="QTY">
<number>2.0</number>
</var>
<var name="ITEMTYPE">
<string>thingy</string>
</var>
</item>
</root>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @STR
SELECT*
FROMOPENXML (@hdoc, '/root/item',8)
WITH(
[type]varchar(20)'var[3]/string',
[id]float'var[1]/number',
[qty]float'var[2]/number'
)AS x
EXEC sp_xml_removedocument @hdoc
===========================================================================
I want a recordset with 1 record and 3 columns... Now, this works just fine... However, If the "VAR" columns in the source XML were to be re-ordered this would break.
Is there a way to say "make the TYPE column = the STRING of the VAR record who's NAME = "ITEMTYPE"... ?
Any ideas?
February 10, 2012 at 6:02 pm
Try this one on. You'll find more info on how to set the inline XML queries up in the W3C xquery examples
declare @xmlstr XML
set @xmlstr=cast(@str as XML);
select item.value('(var[./@name="ITEMID"]/number)[1]','float') ITEMID,
item.value('(var[./@name="ITEMTYPE"]/string)[1]','varchar(100)') ITEMTYPE,
item.value('(var[./@name="QTY"]/number)[1]','float') QTY
from @xmlstr.nodes('/root/item') as node(item)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 11, 2012 at 8:37 am
Genius! That worked like a charm.
Actually I couldn't get the xQuery syntax to work for me as a whole. Instead I just replaced my xPath filters with what you provided and ran it against my OPENXML syntax and it worked. I'm going to go back now and try to get the xQuery to work (becuase I can't let it beat me ;). I did read elsewhere on this forum that OPENXML works faster than xQuery so I'll do some tests.
Either way, THANKS AGAIN!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply