How to get position info from native XML data type
-
I am trying to convert code over to use the native XML data type instead of using sp_xml_preparedocument. But, I am having trouble finding an equivalent way of determining which parent element instance the data came from, aka position information.
The XML comes into the process as ntext and looks something like the following.
-- procedure parameter @i_InfoStringDECLARE @i_InfoString nvarchar(max)
SET @i_InfoString = '<?xml version="1.0" encoding="utf-16"?>
<T><R Table_ID="146" Record_ID="-1" RS="Interface">
<F FN="Maint_Name" EV=" First Sched" T="146" R="-1"/>
<F FN="Maintenance_Due_Date" EV="2008-10-26" L="0" T="146" R="-1"/>
<F FN="Status_ID" EV="1" T="146" R="-1"/>
</R>
<R Table_ID="146" Record_ID="-1" RS="Interface">
<F FN="Maint_Name" EV="Second Sched" T="146" R="-1"/>
<F FN="Maintenance_Due_Date" EV="2009-10-26" L="0" T="146" R="-1"/>
<F FN="Status_ID" EV="10" T="146" R="-1"/>
</R>
</T>'
I need to be able to tell what R element each group of F elements comes from. I can do this with the "old way" without a problem. The actual position number doesn't matter, it just server as identifying all the F elements based on the R element they came from.
DECLARE @InfoString nvarchar(max), @HDoc int
-- Preseve leading spaces, if they exist
IF PATINDEX(N'%" EV=" %', @i_InfoString) > 0
BEGIN
SET @InfoString = @i_InfoString
-- Since the prepare doc is trimming leading spaces, correct those here
SET @InfoString = Replace(Replace(Replace (@InfoString
, N' "', N'!!#32!!"')
, N'" OV=" ', N'" OV="!!#32!!')
, N'" EV=" ', N'" EV="!!#32!!')
EXEC sp_xml_preparedocument @HDoc OUTPUT, @InfoString
END
ELSE
BEGIN
EXEC sp_xml_preparedocument @HDoc OUTPUT, @i_InfoString
END
SELECT Position, Table_ID, Field_Name, Replace(Convert(nVarchar(max), X.Edit_Value), N'!!#32!!', N' ') AS Edit_Value
FROM OPENXML (@HDoc, 'T/R/F')
WITH (Table_ID int '../@Table_ID',
Position int '@mp:parentid',
Field_Name nVarchar(255) '@FN',
Edit_Value nVarchar(max) '@EV') X
ORDER BY Position
-- Make sure the document is destroyed
EXEC sp_xml_removedocument @HDoc
But I haven't managed to find the way to do the equivalent with the native XML data type. Here I don't ave to do the sillyness about leading spaces... but no position info (x.node.value('@mp:parentid', 'int') doesn't work).
DECLARE @XML XML
SET @XML = convert(XML, @i_InfoString)
SELECT NULL Position, x.node.value('@T', 'int') Table_ID
,x.node.value('@FN', 'nvarchar(255)') Field_Name
,x.node.value('@EV', 'nvarchar(max)') Edit_Value
FROM @XML.nodes('T/R/F')AS x(node)
-
SELECT x.node.value('count(for $a in .. return $a/../*[. << $a])','int') Position,x.node.value('@T', 'int') Table_ID
,x.node.value('@FN', 'nvarchar(255)') Field_Name
,x.node.value('@EV', 'nvarchar(max)') Edit_Value
FROM @XML.nodes('T/R/F')AS x(node)
____________________________________________________
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply