How to extract xml data in query

[fanjum]

Hi,
My table has a column with xml data along with other fields. The xml data in xmldata column looks like this.
<?xml version="1.0" encoding="ISO-8859-1"?>
<S:Envelope S:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:S="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="http://www.w3.org/1999/XMLSchema"
xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance">
<S:Body>
<m:NOOP xmlns:m="NOOP">
<PtIns xsi:type="xsd:string">
<Id xsi:type="xsd:int">-1</Id>
<InsuranceId xsi:type="xsd:int">17</InsuranceId>
<EmployeeId xsi:type="xsd:int">564496</PatientId>
</PtIns>
</m:NOOP>
</S:Body>
</S:Envelope>

I need to extract values of InsuranceId for each EmployeeId. but when I try the query below, It complains about a comma ','

SELECT l.id
,l.Deptid
,CAST(l.timestamp AS DATE) AS timestamp

,{EXTRACTVALUE(l.xmldata,'/S:Envelope/S:Body/m:NOOP/PtIns/InsuranceId') } AS InsID

FROM Table l

Your help is MUCH appreciated.

[dougp]

Being specific about the conditions may be helpful.  Is this SQL running as a pass-thru against an Oracle database?

What purpose to the curly braces serve?  I don't see that in the Oracle documentation.

[fanjum]

I'm running this pass-through query against the SQL Server and these curly braces are supposed to send the function without Cognos interfering with it.   

[MFGF]

I'm running this pass-through query against the SQL Server and these curly braces are supposed to send the function without Cognos interfering with it.

I haven't seen queries defined where parts are Cognos SQL and parts are Native or Pass-Thru SQL before. Maybe try defining the entire query as Pass-Thru? Also, if memory serves, single braces {} denote Native SQL, and double braces {{}} denote Pass-Thru SQL?

Cheers!

MF.

[dougp]

EXTRACTVALUE is a function in Oracle, not in SQL Server.

It complains about a comma ','

What is the actual error message?