Given the following SQL Script I try to produce a Result set looking like this
Lable Value
------------------------
UstId_1 DE123456789
ErrorCode 200
BEGIN
declare MyXML XML;
set MyXML = '<params>
<param><value><array><data>
<value><string>UstId_1</string></value>
<value><string>DE123456789</string></value>
</data></array></value></param>
<param><value><array><data>
<value><string>ErrorCode</string></value>
<value><string>200</string></value>
</data></array></value></param>
</params>';
select *
from openxml( MyXML, '/params/param/value/array/data' )
with ( NodeID INT '@mp:id',
ParentID INT '../@mp:id',
NodeValue varchar(2048) 'value',
-- NodeValue2 varchar(2048) 'value[last()]',
NodeText varchar(2048) '@mp:xmltext' );
select *
from openxml( MyXML, '/params/param/value/array/data/value' )
with ( NodeID INT '@mp:id',
ParentID INT '../@mp:id',
NodeValue varchar(2048) 'string',
NodeText varchar(2048) '@mp:xmltext' );
END
The XML is generated by a Web-service I intend to use so I can't change that. I have read in XPath Documents that it is possible to use [2]
or last()
to address a specific element. But I'm not able to figure out how with ASA 10.
I currently have a solution that is using a self join with the result of the second command to get it done.
As this is my first trial with OpenXML() there is a great chance that some of you know a solution with XPath. That's what I'm looking for.
Kind Regards
Thomas
asked
10 Feb '10, 18:00
Thomas Dueme...
2.7k●28●39●65
accept rate:
17%
I honestly don't think this is possible with XPath. Looks like not all the features of XPath are implemented even in SA-11. I've been trying to devise a non-smelling solution for the last hour or so and I can't figure it out.
I think the real question is, "does OpenXML support path expressions in predicates, like [last()]?" ...as Calvin suggests, the answer may be "no".
Stay tuned for an actual answer... :)
Obviously still no actual anwer - but I'm running into the same problem, and using a XPath query like
'//Products[1]'
returns the following hint in 12.0.1.3817:
Therefore I guess that position test predicates like "[1]" or "[position()<3]" or "[last()]" are not (yet) implemented, though the ones using a function name like "last()" simply yield a syntax error...