cancel
Showing results for 
Search instead for 
Did you mean: 

How to Address multiple nodes with the same name with OpenXml

thomas_duemesnil
Participant
6,044

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

Former Member
0 Kudos

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.

Breck_Carter
Participant
0 Kudos

I think the real question is, "does OpenXML support path expressions in predicates, like [last()]?" ...as Calvin suggests, the answer may be "no".

Breck_Carter
Participant
0 Kudos

Stay tuned for an actual answer... 🙂

VolkerBarth
Contributor

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:

Feature 'position tests' not implemented

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...

Accepted Solutions (0)

Answers (0)