XML parsing from ASE - xmlextract query expression

[JTT]

Does anyone know a solution for this: Is it possible in some
way to directly place a sql variable inside a xpath query?
For instance to turn this one:
select xmlextract
('//element[@id="RT200"]//version[4]/@id',pXML returns
char(2)) from xml where filename = 'import_001.xml'
into this one:
select xmlextract
('//element[@id="RT200"]//version[@counter]/@id',pXML
returns char(2)) from xml where filename =
'import_001.xml'
In the last one @counter is interpretated as a xml attribute
– I want it to be a sql variable in order to loop trough
parts of the xml.

I have solved it by constructing the command as a string and
then assign the string to a variable @cmd, and then do "exec
(@cmd)". This means I must use a table in order to return
the extracted xml values out of the exec immidate and into a
variable.

My procedure which parses xml (using external file access
and xpath) and inserts the values into tables is based on
the above since the files contain non-fixed numbers of each
element type in the xml files. I find the last element value
[-1] and loops from the first to the last value using the
exec immidiate method like above. Anyone got a better
method?

xml example:
<element id="RT200">
<version id = a>
version a values....
</version>
<version id = b>
version b values....
</version>
<version id = c>
version c values....
</version>
.. more versions
</element>

[Jeff Tallman]

Tried to respond earlier - post didn't show up - reposting:

xmlextract() takes an expression, consequently you could do:

select xmlextract('//element[@id="RT200"]//version['
+convert(varchar(5),@counter)+']/@id',pXML returns char(2)) from xml
where filename ='import_001.xml'

or you could build the XPATH query into a variable as in

declare @xpath_qry varchar(255)
select @xpath_qry='//element[@id="RT200"]//version['
+convert(varchar(5),@counter)+']/@id'
select xmlextract(@xpath_qry,pXML returns char(2))

from xml
where filename='import_001.xml'

wrote: