The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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%20Duemesnil's gravatar image

Thomas Dueme...
2.4k203357
accept rate: 16%

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.

(10 Feb '10, 23:21) Calvin Allen

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

(11 Feb '10, 11:37) Breck Carter

Stay tuned for an actual answer... :)

(11 Feb '10, 11:39) Breck Carter
Comment Text Removed
1

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

(21 Jan '13, 11:49) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×113
×18
×18

question asked: 10 Feb '10, 18:00

question was seen: 3,073 times

last updated: 21 Jan '13, 11:51