XMLTABLE SQLCODE -16003

Melanie Bowen

XMLTABLE SQLCODE -16003

I'm attempting to use XMLTABLE to pull information from an XML document in DB2 V11 NFM.

The document has some repeating information, which I would like to pull 1 row for each set of information, repeating a specific value.

Below is the XML:

<GetCoverageVerificationByLineResponse xmlns="http://central-insurance.com/WcfPolicyAdminService/2010/08/02">
   <GetCoverageVerificationByLineResult xmlns:b="http://schemas.datacontract.org/2004/07/Central.Ent.PolicyAdmin.DataModel" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
   <b:CoverageParts>
      <b:CoveragePart>
         <b:CoveragePartID>7000</b:CoveragePartID>
         <b:CoveragePartType>AutoLiability</b:CoveragePartType>
         <b:Coverages>
           <b:Coverage>

             <b:Forms>
                  <b:Form>
                       <b:DoNotApplyClaimDollars>false</b:DoNotApplyClaimDollars>
                       <b:EditionDate>0605</b:EditionDate>
                       <b:FormMainCoverages>
                           <b:FormMainCoverage>
                                 <b:Classes/>
                                 <b:CoverageCode>2</b:CoverageCode>

                                 <b:SubCoverages>
                                     <b:FormSubCoverage>
                                           <b:CoverageCode>2</b:CoverageCode>
                                           <b:CoverageCodeDescription>Property Damage</b:CoverageCodeDescription>
             
                                      </b:FormSubCoverage>
                                       <b:FormSubCoverage>
                                             <b:CoverageCode>215</b:CoverageCode>
                                             <b:CoverageCodeDescription>Premiums on appeal bonds</b:CoverageCodeDescription>
            
                                       </b:FormSubCoverage>

                                  </b:SubCoverages>
                             </b:FormMainCoverage>
                      </b:FormMainCoverages>

                  </b:Form>
             </b:Forms>

          </b:Coverage>

         </b:Coverages>

      </b:CoveragePart>

</b:CoverageParts>

  </GetCoverageVerificationByLineResult>
</GetCoverageVerificationByLineResponse>

I'm using the following SQL:

  SELECT X.MANCOVCD, X.SUBCOVCD, X.SUBCOVDS                 
   FROM XMLTABLE(                                           
                    '$d/*:GetCoverageVerificationByLineRespon
'se/*:GetCoverageVerificationByLineResult/*:CoverageParts/*:C
'overagePart/*:Coverages/*:Coverage/*:Forms/*:Form/*:FormMain
'Coverages/*:FormMainCoverage'                              
    PASSING CAST(:PASS-RESP-BODY1 AS XML) AS "d"            
   COLUMNS                                                  
MANCOVCD    CHAR(03) path '../*:CoverageCode',              
SUBCOVCD    CHAR(03) path '*:SubCoverages/*:FormSubCoverage/*
':CoverageCode',                                            
SUBCOVDS    CHAR(100) path '*:SubCoverages/*:FormSubCoverage/
'*:CoverageCodeDescription')                                
   AS X        

The XML will have multiple sub coverage codes and sub coverage descriptions for any one main coverage code. I would like the following results set:

MANCOVCD      SUBCOVCD     SUBCOVDS   

2                               2                   Property Damage

2                               215               Premiums on appeal bonds

 

I receive the following error when executing the above SQL:

SQLCODE = -16003, ERROR:  AN EXPRESSION OF DATA TYPE ( item(), item()+
) CANNOT BE USED WHEN THE DATA TYPE item() IS EXPECTED IN THE CONTEXT.
ERROR QNAME=err:XPTY0004                                             
SQLSTATE   = 10507 SQLSTATE RETURN CODE                              
SQLERRP    = DSNNQIV SQL PROCEDURE DETECTING ERROR                   
SQLERRD    = -100  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION         
SQLERRD    = X'FFFFFF9C'  X'00000000'  X'00000000'  X'FFFFFFFF'      
X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION     

Any help you could provide would be much appreciated.

Regards,

Melanie              

 

 

 

 

 

 

 

  

Natalie Faulkner

XMLTABLE SQLCODE -16003
(in response to Melanie Bowen)
Melanie,


You may want to try something like the following:

select
.
.
.
,XMLTABLE('($e//Subcoverages/FormSubCoverage)' PASSING “your xml column name” AS "e"
COLUMNS
MANCOVCD CHAR(03) path '../CoverageCode'
,SUBCOVCD CHAR(03) path 'CoverageCode’
,SUBCOVDS CHAR(100) path 'CoverageCodeDescription'
) AS X
where xxxxx
order by xxxxx
;


This format worked for me when I was facing the same type of issue. Hope you find it helpful!!


Natalie W Faulkner
WV Office of Technology – Data Center
Charleston, WV 25305




From: Melanie Bowen [mailto:[login to unmask email]
Sent: Wednesday, November 01, 2017 12:33 PM
To: [login to unmask email]
Subject: [DB2-L] - XMLTABLE SQLCODE -16003


I'm attempting to use XMLTABLE to pull information from an XML document in DB2 V11 NFM.

The document has some repeating information, which I would like to pull 1 row for each set of information, repeating a specific value.

Below is the XML:




7000
AutoLiability





false
0605



2



2
Property Damage



215
Premiums on appeal bonds


















I'm using the following SQL:

SELECT X.MANCOVCD, X.SUBCOVCD, X.SUBCOVDS
FROM XMLTABLE(
'$d/*:GetCoverageVerificationByLineRespon
'se/*:GetCoverageVerificationByLineResult/*:CoverageParts/*:C
'overagePart/*:Coverages/*:Coverage/*:Forms/*:Form/*:FormMain
'Coverages/*:FormMainCoverage'
PASSING CAST(:PASS-RESP-BODY1 AS XML) AS "d"
COLUMNS
MANCOVCD CHAR(03) path '../*:CoverageCode',
SUBCOVCD CHAR(03) path '*:SubCoverages/*:FormSubCoverage/*
':CoverageCode',
SUBCOVDS CHAR(100) path '*:SubCoverages/*:FormSubCoverage/
'*:CoverageCodeDescription')
AS X

The XML will have multiple sub coverage codes and sub coverage descriptions for any one main coverage code. I would like the following results set:

MANCOVCD SUBCOVCD SUBCOVDS

2 2 Property Damage

2 215 Premiums on appeal bonds



I receive the following error when executing the above SQL:

SQLCODE = -16003, ERROR: AN EXPRESSION OF DATA TYPE ( item(), item()+
) CANNOT BE USED WHEN THE DATA TYPE item() IS EXPECTED IN THE CONTEXT.
ERROR QNAME=err:XPTY0004
SQLSTATE = 10507 SQLSTATE RETURN CODE
SQLERRP = DSNNQIV SQL PROCEDURE DETECTING ERROR
SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

Any help you could provide would be much appreciated.

Regards,

Melanie

















-----End Original Message-----

Shay Miller

RE: XMLTABLE SQLCODE -16003
(in response to Melanie Bowen)

I had a similar problem.

when working with XML that have namespaces (in your case b is a namespace), you need to declare it in the XMLTABLE  statement.

 

you need to declare it everywhere that you specify a path in the xml.

 

in your case, it should look something like this:

 

SELECT X.MANCOVCD, X.SUBCOVCD, X.SUBCOVDS                 
   FROM XMLTABLE(  

' declare namespace b="http://schemas.datacontract.org/2004/07/Central.Ent.PolicyAdmin.DataModel" ;                                          
                    $d/*:GetCoverageVerificationByLineRespon
'se/*:GetCoverageVerificationByLineResult/*:CoverageParts/*:C
'overagePart/*:Coverages/*:Coverage/*:Forms/*:Form/*:FormMain
'Coverages/*:FormMainCoverage'                              
    PASSING CAST(:PASS-RESP-BODY1 AS XML) AS "d"            
   COLUMNS                                                   MANCOVCD CHAR(03)
path 'declare namespace b="http://schemas.datacontract.org/2004/07/Central.Ent.PolicyAdmin.DataModel" ; ../*:CoverageCode',
SUBCOVCD CHAR(03)
path 'declare namespace b="http://schemas.datacontract.org/2004/07/Central.Ent.PolicyAdmin.DataModel" ; *:SubCoverages/*:FormSubCoverage/*
':CoverageCode',
SUBCOVDS CHAR(100)
path 'declare namespace b="http://schemas.datacontract.org/2004/07/Central.Ent.PolicyAdmin.DataModel" ;
*:SubCoverages/*:FormSubCoverage/*:CoverageCodeDescription')
AS X

 

Shay