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

(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:

×119
×47
×29

question asked: 10 Feb '10, 18:00

question was seen: 5,565 times

last updated: 21 Jan '13, 11:51