Probm with default date during reading xml file

[Alexis.A]

Hi,

I try to create an sqlrpgle program for reading XML file to insert data in table.

I have a problem when i read the xml file.

Example of XML file :

<?xml version="1.0" encoding="UTF-8"?>
<root>
<document_name>20201009_000156_001.pdf</document_name>
<payment>
<deadline/>
<mode/>
</payment>
<invoice>
<date>2020-10-08</date>
<reference>2211325</reference>
</invoice>
</root>

This is the sql instruction I use :

Select
Xml_nompdf,
Xml_datpay,
Xml_modpay,
Xml_datfac,
Xml_numfac
From

XMLTABLE (
'$d/root' passing XMLPARSE (DOCUMENT GET_XML_FILE(
'/home/transfert/KOPROO/in' concat '/' concat trim('02081202010090001561.xml')))
as "d"
Columns
Xml_nompdf char(50) path 'document_name',
Xml_datpay date default '0001-01-01' path 'payment/deadline',
Xml_modpay char(30) path 'payment/mode',
Xml_datfac date path 'invoice/date',
Xml_numfac char(50) path 'invoice/reference')

as X with NC

The problem is when I run the SQL, I have an error 16061
The value of the path 'payment/deadline' is empty.


Do you have a solution for this problem?

Thanks you

[Birgitta Hauser]

You get an Error because "nothing" is an invalid date.
I'd always return a date or a number as character value (except the XML document is checked against and XML Schema) and then convert it in my SELECT-statement.
Something like this:

Select xml_nompdf,
       Date(Case When XML_DatPay <> '' Then XML_DatPay Else '0001-01-01' End) as xml_DatPay
  from XMLTABLE('/root'
          Passing XMLPARSE(Document YourXMLDocument)
          Columns xml_nompdf Char(50)    path 'document_name',
                  xml_datpay VarChar(10) path 'payment/deadline'
                  ... ) x; 

Birgitta

[Alexis.A]

Hello Brigitta

Thanks for the answer.

It's work