on 2010 Feb 10 6:00 PM
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
Request clarification before answering.
User | Count |
---|---|
75 | |
30 | |
9 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.