(sans sujet)
Posté : lun. 21 oct. 2019, 14:05:11
Bonjour,
Je rencontre un problème pour découper du xml avec le xmltable.
J’ai une structure comme suit
<?xml version="1.0" encoding="UTF-8"?>
<podPoc>
<journey>
<routeId>PV-TEST107028104</routeId>
<site id="PAP-002" >
<consignment type="D">
<orderRef>JD1095-12123456</orderRef>
<status>X</status>
<consignmentDetail>
<detailRef>445567890245</detailRef>
<customerDetailRef>Palette 1 Cuisine Centrale</customerDetailRef>
<consignmentDetail>
<detailRef>000000015472</detailRef>
<customerDetailRef>Carton Bouillon de Volaille Knorr</customerDetailRef>
</consignmentDetail>
<consignmentDetail>
<detailRef>0000000150212</detailRef>
<customerDetailRef>Carton Tubes 1kg Nutella</customerDetailRef>
</consignmentDetail>
</consignmentDetail>
<consignmentDetail>
<detailRef>4234567890245</detailRef>
<customerDetailRef>Roll Metallique 1 Frais Cuisine Centrale</customerDetailRef>
<consignmentDetail>
<detailRef>0000000037668</detailRef>
<customerDetailRef>Jambon cuit superieur Label Rouge origine France 6,5Kg</customerDetailRef>
<clauseInfo>
<clause>CTR</clause>
<clauseQty>-2</clauseQty>
</clauseInfo>
</consignmentDetail>
<consignmentDetail>
<detailRef>0000000039439</detailRef>
<customerDetailRef>Carton de 6 Creme Excellence Cuisine et Patisserie 35%MG 1L</customerDetailRef>
</consignmentDetail>
</consignmentDetail>
</consignment>
<consignment type="D">
<orderRef>JD1095-12123465</orderRef>
<status>R</status>
<clauseInfo>
<clause>CTR</clause>
<description>Casse - Réfusée</description>
</clauseInfo>
<consignmentDetail>
<detailRef>1234567890123</detailRef>
<customerDetailRef>Palette 1 Cuisine Centrale</customerDetailRef>
<consignmentDetail>
<detailRef>1234567890130</detailRef>
<customerDetailRef>Carton Pasta Pro 5Kg</customerDetailRef>
</consignmentDetail>
</consignmentDetail>
</consignment>
<task taskName="Livraison Emballages" taskStartTime="2019-10-17T21:02:56" taskEndTime="2019-10-17T21:03:37.590" />
<task taskName="Reprise Emballage" taskStartTime="2019-10-17T21:03:37.977" taskEndTime="2019-10-17T21:03:39.657" />
</site>
</journey>
</podPoc>
Et je n’arrive pas a découper le xml avec du sql de façon à tous ramener toutes les données en une seule fois.
Voici ce que j’ai essayer :
SELECT *
FROM
XMLTABLE('$result' PASSING XMLPARSE(DOCUMENT GET_XML_FILE('fichier_test_xml.xml') ) as "result"
COLUMNS
journey_routeId varchar(100) PATH 'podPoc/journey/routeId',
site_id varchar(100) PATH 'podPoc/journey/site/@id',
consignment_type varchar(100) PATH 'podPoc/journey/site/consignment/@type',
consignment_orderRef varchar(100) PATH 'podPoc/journey/site/consignment/orderRef',
consignment_status varchar(100) PATH 'podPoc/journey/site/consignment/status',
consignmentDetail_1_detailRef varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/detailRef',
consignmentDetail_1_customerDetailRef varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/customerDetailRef',
clauseInfo_clause varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/clauseInfo/clause',
clauseInfo_clauseQty varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/clauseInfo/clauseQty',
consignmentDetail_2_detailRef varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/consignmentDetail/detailRef',
consignmentDetail_2_customerDetailRef varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/consignmentDetail/customerDetailRef',
clauseInfo_2_clause varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/consignmentDetail/clauseInfo/clause',
clauseInfo_2_clauseQty varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/consignmentDetail/clauseInfo/clauseQty',
taskName varchar(100) PATH 'podPoc/journey/site/taskName',
taskStartTime varchar(100) PATH 'podPoc/journey/site/taskStartTime'
) AS tmp
Merci pour votre aide.
Cordailement
Je rencontre un problème pour découper du xml avec le xmltable.
J’ai une structure comme suit
<?xml version="1.0" encoding="UTF-8"?>
<podPoc>
<journey>
<routeId>PV-TEST107028104</routeId>
<site id="PAP-002" >
<consignment type="D">
<orderRef>JD1095-12123456</orderRef>
<status>X</status>
<consignmentDetail>
<detailRef>445567890245</detailRef>
<customerDetailRef>Palette 1 Cuisine Centrale</customerDetailRef>
<consignmentDetail>
<detailRef>000000015472</detailRef>
<customerDetailRef>Carton Bouillon de Volaille Knorr</customerDetailRef>
</consignmentDetail>
<consignmentDetail>
<detailRef>0000000150212</detailRef>
<customerDetailRef>Carton Tubes 1kg Nutella</customerDetailRef>
</consignmentDetail>
</consignmentDetail>
<consignmentDetail>
<detailRef>4234567890245</detailRef>
<customerDetailRef>Roll Metallique 1 Frais Cuisine Centrale</customerDetailRef>
<consignmentDetail>
<detailRef>0000000037668</detailRef>
<customerDetailRef>Jambon cuit superieur Label Rouge origine France 6,5Kg</customerDetailRef>
<clauseInfo>
<clause>CTR</clause>
<clauseQty>-2</clauseQty>
</clauseInfo>
</consignmentDetail>
<consignmentDetail>
<detailRef>0000000039439</detailRef>
<customerDetailRef>Carton de 6 Creme Excellence Cuisine et Patisserie 35%MG 1L</customerDetailRef>
</consignmentDetail>
</consignmentDetail>
</consignment>
<consignment type="D">
<orderRef>JD1095-12123465</orderRef>
<status>R</status>
<clauseInfo>
<clause>CTR</clause>
<description>Casse - Réfusée</description>
</clauseInfo>
<consignmentDetail>
<detailRef>1234567890123</detailRef>
<customerDetailRef>Palette 1 Cuisine Centrale</customerDetailRef>
<consignmentDetail>
<detailRef>1234567890130</detailRef>
<customerDetailRef>Carton Pasta Pro 5Kg</customerDetailRef>
</consignmentDetail>
</consignmentDetail>
</consignment>
<task taskName="Livraison Emballages" taskStartTime="2019-10-17T21:02:56" taskEndTime="2019-10-17T21:03:37.590" />
<task taskName="Reprise Emballage" taskStartTime="2019-10-17T21:03:37.977" taskEndTime="2019-10-17T21:03:39.657" />
</site>
</journey>
</podPoc>
Et je n’arrive pas a découper le xml avec du sql de façon à tous ramener toutes les données en une seule fois.
Voici ce que j’ai essayer :
SELECT *
FROM
XMLTABLE('$result' PASSING XMLPARSE(DOCUMENT GET_XML_FILE('fichier_test_xml.xml') ) as "result"
COLUMNS
journey_routeId varchar(100) PATH 'podPoc/journey/routeId',
site_id varchar(100) PATH 'podPoc/journey/site/@id',
consignment_type varchar(100) PATH 'podPoc/journey/site/consignment/@type',
consignment_orderRef varchar(100) PATH 'podPoc/journey/site/consignment/orderRef',
consignment_status varchar(100) PATH 'podPoc/journey/site/consignment/status',
consignmentDetail_1_detailRef varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/detailRef',
consignmentDetail_1_customerDetailRef varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/customerDetailRef',
clauseInfo_clause varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/clauseInfo/clause',
clauseInfo_clauseQty varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/clauseInfo/clauseQty',
consignmentDetail_2_detailRef varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/consignmentDetail/detailRef',
consignmentDetail_2_customerDetailRef varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/consignmentDetail/customerDetailRef',
clauseInfo_2_clause varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/consignmentDetail/clauseInfo/clause',
clauseInfo_2_clauseQty varchar(100) PATH 'podPoc/journey/site/consignment/consignmentDetail/consignmentDetail/clauseInfo/clauseQty',
taskName varchar(100) PATH 'podPoc/journey/site/taskName',
taskStartTime varchar(100) PATH 'podPoc/journey/site/taskStartTime'
) AS tmp
Merci pour votre aide.
Cordailement