Hi all, I'm trying to parse a large XML file into an oracle table and getting the error below:
SQL> INSERT INTO THIS_XML (Client,"Date",SequenceNumber,NbMessages,version,relea
se,agency,domain,function,controlNumber,pnrPurgeDate,"number",companyId,controlN
umber2,date3,time,typeOfPnrElement,agentId,officeId,iataCode,queueingOfficeId,ci
tyCode,creationOfficeId,agentSignature,creationDate,creatorIataCode,creationTime
,quantity,status,officeId4,agentCode,receiveFrom,originatorId,inHouseIdentificat
ion1,originatorTypeCode,companyId5,locationId,codedCountry,originatorId6,inHouse
Identification17,originatorTypeCode8,companyId9,locationId10,codedCountry11,inHo
useIdentification112,originatorTypeCode13,companyId14,locationId15,codedCountry1
6,qualifier,number17,segmentName,surname,quantity18,firstName,originDestination,
qualifier19,number20,segmentName21,depDate,depTime,arrDate,arrTime,dayChangeIndi
cator,cityCode22,cityCode23,identification,identification24,classOfService,detai
l,qualifier25,date26,time27,status28,function29,companyId30,controlNumber31,quan
tity32,status33,equipment,weekDay,arrivalTerminal,departTerminal,"option",origin
atorId34,inHouseIdentification135,originatorTypeCode36,companyId37,locationId38,
codedCountry39,trueLocationId,trueLocationId40,carrierCode,flightNumber,bookingC
lass,flightIndicator,codeShareAgreement,cabinCode,subclassId,sourceQualifier1,co
untryCode,systemCode,groupingCode,marriageQualifier,tatooNum,marker2,qualifier41
,number42,segmentName43,subjectQualifier,type,longFreetext,indicator,date44,offi
ceId45,marker3,actionRequestCode)
2 With t AS (SELECT xmltype (bfilename('MYXML','prdsbr.T160640'), nls_charset
_id('WE8ISO8859P1')) xmlcol FROM dual)
3 SELECT
4 extractValue(value(x), '/ROW/Client') client
5 ,extractValue(value(x),'/ROW/"Date"')"Date"
6 ,extractValue(value(x),'/ROW/SequenceNumber')SequenceNumber
7 ,extractValue(value(x),'/ROW/NbMessages')NbMessages
8 ,extractValue(value(x),'/ROW/version')version
9 ,extractValue(value(x),'/ROW/release')release
10 ,extractValue(value(x),'/ROW/agency')agency
11 ,extractValue(value(x),'/ROW/domain')domain
12 ,extractValue(value(x),'/ROW/"function"')"function"
13 ,extractValue(value(x),'/ROW/controlNumber')controlNumber
14 ,extractValue(value(x),'/ROW/pnrPurgeDate')pnrPurgeDate
15 ,extractValue(value(x),'/ROW/"number"')"number"
16 ,extractValue(value(x),'/ROW/companyId')companyId
17 ,extractValue(value(x),'/ROW/controlNumber2')controlNumber2
18 ,extractValue(value(x),'/ROW/date3')date3
19 ,extractValue(value(x),'/ROW/time')time
20 ,extractValue(value(x),'/ROW/typeOfPnrElement')typeOfPnrElement
21 ,extractValue(value(x),'/ROW/agentId')agentId
22 ,extractValue(value(x),'/ROW/officeId')officeId
23 ,extractValue(value(x),'/ROW/iataCode')iataCode
24 ,extractValue(value(x),'/ROW/queueingOfficeId')queueingOfficeId
25 ,extractValue(value(x),'/ROW/cityCode')cityCode
26 ,extractValue(value(x),'/ROW/creationOfficeId')creationOfficeId
27 ,extractValue(value(x),'/ROW/agentSignature')agentSignature
28 ,extractValue(value(x),'/ROW/creationDate')creationDate
29 ,extractValue(value(x),'/ROW/creatorIataCode')creatorIataCode
30 ,extractValue(value(x),'/ROW/creationTime')creationTime
31 ,extractValue(value(x),'/ROW/quantity')quantity
32 ,extractValue(value(x),'/ROW/status')status
33 ,extractValue(value(x),'/ROW/officeId4')officeId4
34 ,extractValue(value(x),'/ROW/agentCode')agentCode
35 ,extractValue(value(x),'/ROW/receiveFrom')receiveFrom
36 ,extractValue(value(x),'/ROW/originatorId')originatorId
37 ,extractValue(value(x),'/ROW/inHouseIdentification1')inHouseIdentification1
38 ,extractValue(value(x),'/ROW/originatorTypeCode')originatorTypeCode
39 ,extractValue(value(x),'/ROW/companyId5')companyId5
40 ,extractValue(value(x),'/ROW/locationId')locationId
41 ,extractValue(value(x),'/ROW/codedCountry')codedCountry
42 ,extractValue(value(x),'/ROW/originatorId6')originatorId6
43 ,extractValue(value(x),'/ROW/inHouseIdentification17')inHouseIdentification
17
44 ,extractValue(value(x),'/ROW/originatorTypeCode8')originatorTypeCode8
45 ,extractValue(value(x),'/ROW/companyId9')companyId9
46 ,extractValue(value(x),'/ROW/locationId10')locationId10
47 ,extractValue(value(x),'/ROW/codedCountry11')codedCountry11
48 ,extractValue(value(x),'/ROW/inHouseIdentification112')inHouseIdentificatio
n112
49 ,extractValue(value(x),'/ROW/originatorTypeCode13')originatorTypeCode13
50 ,extractValue(value(x),'/ROW/companyId14')companyId14
51 ,extractValue(value(x),'/ROW/locationId15')locationId15
52 ,extractValue(value(x),'/ROW/codedCountry16')codedCountry16
53 ,extractValue(value(x),'/ROW/qualifier')qualifier
54 ,extractValue(value(x),'/ROW/number17')number17
55 ,extractValue(value(x),'/ROW/segmentName')segmentName
56 ,extractValue(value(x),'/ROW/surname')surname
57 ,extractValue(value(x),'/ROW/quantity18')quantity18
58 ,extractValue(value(x),'/ROW/firstName')firstName
59 ,extractValue(value(x),'/ROW/originDestination')originDestination
60 ,extractValue(value(x),'/ROW/qualifier19')qualifier19
61 ,extractValue(value(x),'/ROW/number20')number20
62 ,extractValue(value(x),'/ROW/segmentName21')segmentName21
63 ,extractValue(value(x),'/ROW/depDate')depDate
64 ,extractValue(value(x),'/ROW/depTime')depTime
65 ,extractValue(value(x),'/ROW/arrDate')arrDate
66 ,extractValue(value(x),'/ROW/arrTime')arrTime
67 ,extractValue(value(x),'/ROW/dayChangeIndicator')dayChangeIndicator
68 ,extractValue(value(x),'/ROW/cityCode22')cityCode22
69 ,extractValue(value(x),'/ROW/cityCode23')cityCode23
70 ,extractValue(value(x),'/ROW/identification')identification
71 ,extractValue(value(x),'/ROW/identification24')identification24
72 ,extractValue(value(x),'/ROW/classOfService')classOfService
73 ,extractValue(value(x),'/ROW/detail')detail
74 ,extractValue(value(x),'/ROW/qualifier25')qualifier25
75 ,extractValue(value(x),'/ROW/date26')date26
76 ,extractValue(value(x),'/ROW/time27')time27
77 ,extractValue(value(x),'/ROW/status28')status28
78 ,extractValue(value(x),'/ROW/function29')function29
79 ,extractValue(value(x),'/ROW/companyId30')companyId30
80 ,extractValue(value(x),'/ROW/controlNumber31')controlNumber31
81 ,extractValue(value(x),'/ROW/quantity32')quantity32
82 ,extractValue(value(x),'/ROW/status33')status33
83 ,extractValue(value(x),'/ROW/equipment')equipment
84 ,extractValue(value(x),'/ROW/weekDay')weekDay
85 ,extractValue(value(x),'/ROW/arrivalTerminal')arrivalTerminal
86 ,extractValue(value(x),'/ROW/departTerminal')departTerminal
87 ,extractValue(value(x),'/ROW/"option"')"option"
88 ,extractValue(value(x),'/ROW/originatorId34')originatorId34
89 ,extractValue(value(x),'/ROW/inHouseIdentification135')inHouseIdentificatio
n135
90 ,extractValue(value(x),'/ROW/originatorTypeCode36')originatorTypeCode36
91 ,extractValue(value(x),'/ROW/companyId37')companyId37
92 ,extractValue(value(x),'/ROW/locationId38')locationId38
93 ,extractValue(value(x),'/ROW/codedCountry39')codedCountry39
94 ,extractValue(value(x),'/ROW/trueLocationId')trueLocationId
95 ,extractValue(value(x),'/ROW/trueLocationId40')trueLocationId40
96 ,extractValue(value(x),'/ROW/carrierCode')carrierCode
97 ,extractValue(value(x),'/ROW/flightNumber')flightNumber
98 ,extractValue(value(x),'/ROW/bookingClass')bookingClass
99 ,extractValue(value(x),'/ROW/flightIndicator')flightIndicator
100 ,extractValue(value(x),'/ROW/codeShareAgreement')codeShareAgreement
101 ,extractValue(value(x),'/ROW/cabinCode')cabinCode
102 ,extractValue(value(x),'/ROW/subclassId')subclassId
103 ,extractValue(value(x),'/ROW/sourceQualifier1')sourceQualifier1
104 ,extractValue(value(x),'/ROW/countryCode')countryCode
105 ,extractValue(value(x),'/ROW/systemCode')systemCode
106 ,extractValue(value(x),'/ROW/groupingCode')groupingCode
107 ,extractValue(value(x),'/ROW/marriageQualifier')marriageQualifier
108 ,extractValue(value(x),'/ROW/tatooNum')tatooNum
109 ,extractValue(value(x),'/ROW/marker2')marker2
110 ,extractValue(value(x),'/ROW/qualifier41')qualifier41
111 ,extractValue(value(x),'/ROW/number42')number42
112 ,extractValue(value(x),'/ROW/segmentName43')segmentName43
113 ,extractValue(value(x),'/ROW/subjectQualifier')subjectQualifier
114 ,extractValue(value(x),'/ROW/type')type
115 ,extractValue(value(x),'/ROW/longFreetext')longFreetext
116 ,extractValue(value(x),'/ROW/indicator')indicator
117 ,extractValue(value(x),'/ROW/date44')date44
118 ,extractValue(value(x),'/ROW/officeId45')officeId45
119 ,extractValue(value(x),'/ROW/marker3')marker3
120 ,extractValue(value(x),'/ROW/actionRequestCode')actionRequestCode
121 FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/ROWSET/ROW'))) x;
With t AS (SELECT xmltype (bfilename('MYXML','prdsbr.T160640'), nls_charset_id('
WE8ISO8859P1')) xmlcol FROM dual)
*
ERROR at line 2:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: invalid character 47 ('/') found in a Name or Nmtoken
ORA-06512: at "SYS.XMLTYPE", line 296
ORA-06512: at line 1