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,056

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

Accepted Solutions (0)

Answers (0)