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.
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.
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.
Quote from: fanjum on 18 Aug 2023 09:16:37 AM
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.
EXTRACTVALUE is a function in Oracle, not in SQL Server.
QuoteIt complains about a comma ','
What is the actual error message?