'How to fix Only one top level element is allowed in an XML document error when parsing and A string literal was not closed error into SQL Server
I am trying to extract the data from an XML file that has many duplicates and if I change the name it is not showing anything in the result, I am not able to do it as I am using below code
DECLARE @xml_text VARCHAR(max), @i INT
SELECT @xml_text = '<?xml version="1.0" encoding="UTF-8"?>
<LIST>
<ControlSegment EDIVERSION="5010" EDIMESSAGECODE="100" EDIMESSAGEFUNCTION="SM" TESTINDICATOR="P" ISACONTROLNUMBER="002125" GSCONTROLNUMBER="002125" SENDERQUALIFIER="14" SENDERID="NEWEFRIL00" RECEIVERQUALIFIER="02" RECEIVERID="C020C" />
<ISA authorizationInformationQualifier="00" authorizationInformation=" " securityInformationQualifier="00" securityInformation=" " interchangeIDQualifier="14" interchangeSenderID="NEWEFRIL00" interchangeIDQualifier2="02" interchangeReceiverID="C020C" interchangeDate="2022-05-06T12:04:00" interchangeTime="12:04:00" repetitionSeparator="U" interchangeControlVersionNumber="00401" interchangeControlNumber="002125" acknowledgmentRequested="0" interchangeUsageIndicator="P" componentElementSeparator="|">
<GS functionalIdentifierCode="SM" applicationSendersCode="006922827NWL1" applicationReceiversCode="RANGER" date="2022-05-06T12:04:00" time="12:04:00" groupControlNumber="002125" responsibleAgencyCode="X" versionReleaseIndustryIdentifierCode="004010">
<ST transactionSetIdentifierCode="100" transactionSetControlNumber="002125">
<B2 standardCarrierAlphaCode="RANGER" shipmentIdentificationNumber="64355525" weightUnitCode="L" shipmentMethodofPayment="PP" shipmentQualifier="L" totalEquipment="0.0" />
<B2A transactionSetPurposeCode="01" applicationType="LT" />
<L11 referenceIdentification="RANGER" referenceIdentificationQualifier="SCA" />
<L11 referenceIdentification="Ryder" referenceIdentificationQualifier="ZZ" />
<G62 dateQualifier="64" date="2022-05-05T14:20:00" timeQualifier="1" time="14:20:00" timeCode="ET" />
<NTE noteReferenceCode="TRA" description="CFA 509-575-5500 transportation use up to date routing guide" />
<loop0300>
<S5 stopSequenceNumber="1" stopReasonCode="LD" weight="43069.2" weightUnitCode="L" numberofUnitsShipped="0" unitorBasisforMeasurementCode="PC" volume="2554.92" volumeUnitQualifier="E">
<G62 dateQualifier="96" date="2022-06-03T16:30:00" timeQualifier="Y" time="16:30:00" timeCode="ED" />
<PLD quantityofPalletsShipped="56" weight="0.0" />
<NTE noteReferenceCode="TRA" description="CFA 509-575-5500 transportation use up to date routing guide" />
<loop0310>
<N1 entityIdentifierCode="SF" name="SAI Services" identificationCodeQualifier="93" identificationCode="2821">
<N3 addressInformation="Gamers Colony" />
<N4 cityName="SAI Services" stateorProvinceCode="IN" postalCode="500341" countryCode="HYD" />
<G61 contactFunctionCode="SH" name="CONTACT CONTACT" communicationNumberQualifier="TE" communicationNumber="123-456-7890" />
</N1>
</loop0310>
<loop0350>
<OID referenceIdentification="2219274061" purchaseOrderNumber="Z45LQ" packagingFormCode="PC" quantity="0" weightUnitCode="L" weight="43069.2" volumeUnitQualifier="C" volume="2554.92">
<loop0360>
<L5 ladingLineItemNumber="0.0" commodityCode="MISC" commodityCodeQualifier="N">
<AT8 weightQualifier="G" weightUnitCode="L" weight="43069.2" ladingQuantity="0.0" ladingQuantity2="0.0" volumeUnitQualifier="C" volume="2554.92" />
<loop0365>
<G61 contactFunctionCode="ZZ" name="ZZ">
<L11 referenceIdentification="Z45LQ" referenceIdentificationQualifier="DJ" />
</G61>
</loop0365>
</L5>
</loop0360>
</OID>
</loop0350>
</S5>
</loop0300>
<loop0300>
<S5 stopSequenceNumber="2" stopReasonCode="UL" weight="43069.2" weightUnitCode="L" numberofUnitsShipped="0" unitorBasisforMeasurementCode="PC" volume="2554.92" volumeUnitQualifier="E">
<G62 dateQualifier="97" date="2022-06-08T07:00:00" timeQualifier="Z" time="07:00:00" timeCode="PD" />
<PLD quantityofPalletsShipped="56" weight="0.0" />
<NTE noteReferenceCode="TRA" description="CFA 509-575-5500 transportation use up to date routing guide" />
<loop0310>
<N1 entityIdentifierCode="ST" name="ACE HDWE RETAIL SUPP CTR-9741" identificationCodeQualifier="93" identificationCode="17746">
<N3 addressInformation="200 GRANT WAY" />
<N4 cityName="GS Ground" stateorProvinceCode="WA" postalCode="500341" countryCode="HYD" />
</N1>
</loop0310>
<loop0350>
<OID referenceIdentification="2219274061" purchaseOrderNumber="Z45LQ" packagingFormCode="PC" quantity="0" weightUnitCode="L" weight="43069.2" volumeUnitQualifier="C" volume="2554.92">
<loop0360>
<L5 ladingLineItemNumber="0.0" commodityCode="MISC" commodityCodeQualifier="N">
<AT8 weightQualifier="G" weightUnitCode="L" weight="43069.2" ladingQuantity="0.0" ladingQuantity2="0.0" volumeUnitQualifier="C" volume="2554.92" />
<loop0365>
<G61 contactFunctionCode="ZZ" name="ZZ">
<L11 referenceIdentification="Z45LQ" referenceIdentificationQualifier="DJ" />
</G61>
</loop0365>
</L5>
</loop0360>
</OID>
</loop0350>
</S5>
</loop0300>
<L3 weight="0" weightQualifier="G" freightRate="0" amountCharged="0" advances="0.0" prepaidAmount="0.0" volume="0" volumeUnitQualifier="E" ladingQuantity="0" weightUnitCode="L" declaredValue="0" rateValueQualifier2="PL" />
<SE numberofIncludedSegments="32" transactionSetControlNumber="002125" />
</ST>
<GE numberofTransactionSetsIncluded="1" groupControlNumber="002125" />
</GS>
<IEA numberofIncludedFunctionalGroups="1" interchangeControlNumber="002125" />
</ISA>
</LIST>';
EXEC sp_xml_preparedocument @i OUTPUT, @xml_text
SELECT *
FROM OPENXML(@i, '/LIST/',1)
--cross apply OPENXML(@i, '/LIST/ISA',2)
WITH
(
EDIVERSION [varchar](50) '../@EDIVERSION',
EDIMESSAGECODE [varchar](50) '../@EDIMESSAGECODE',
EDIMESSAGEFUNCTION [varchar](50) '../@EDIMESSAGEFUNCTION',
TESTINDICATOR [varchar](50) '../@TESTINDICATOR',
ISACONTROLNUMBER [varchar](50) '../@ISACONTROLNUMBER',
GSCONTROLNUMBER [varchar](50) '../@GSCONTROLNUMBER',
SENDERQUALIFIER [varchar](50) '../@SENDERQUALIFIER',
RECEIVERQUALIFIER [varchar](50) '../@RECEIVERQUALIFIER',
RECEIVERID [varchar](50) '../@RECEIVERID',
authorizationInformationQualifier [varchar](50) '../@authorizationInformationQualifier',
authorizationInformation [varchar](50) '../@authorizationInformation',
securityInformationQualifier [varchar](50) '../@securityInformationQualifier',
securityInformation [varchar](50) '../@securityInformation',
interchangeIDQualifier [varchar](50) '../@interchangeIDQualifier',
interchangeSenderID [varchar](50) '../@interchangeSenderID',
interchangeIDQualifier [varchar](50) '../@interchangeIDQualifier',
interchangeReceiverID [varchar](50) '../@interchangeReceiverID',
interchangeDate [varchar](50) '../@interchangeDate',
repetitionSeparator [varchar](50) '../@repetitionSeparator',
interchangeControlVersionNumber [varchar](50) '../@interchangeControlVersionNumber',
interchangeControlNumber [varchar](50) '../@interchangeControlNumber',
acknowledgmentRequested [varchar](50) '../@acknowledgmentRequested',
interchangeUsageIndicator [varchar](50) '../@interchangeUsageIndicator',
componentElementSeparator [varchar](50) '../@componentElementSeparator',
functionalIdentifierCode [varchar](50) '../@functionalIdentifierCode',
applicationSendersCode [varchar](50) '../@applicationSendersCode',
[date] [varchar](50) '../@date',
[time] [varchar](50) '../@time',
groupControlNumber [varchar](50) '../@groupControlNumber',
responsibleAgencyCode [varchar](50) '../@responsibleAgencyCode',
versionReleaseIndustryIdentifierCode [varchar](50) '../@versionReleaseIndustryIdentifierCode',
transactionSetIdentifierCode [varchar](50) '@transactionSetIdentifierCode',
transactionSetControlNumber [varchar](50) '@transactionSetControlNumber',
standardCarrierAlphaCode [varchar](50) '@standardCarrierAlphaCode',
shipmentIdentificationNumber [varchar](50) '@shipmentIdentificationNumber',
weightUnitCode1 [varchar](50) '@weightUnitCode1',
shipmentMethodofPayment [varchar](50) '@shipmentMethodofPayment',
shipmentQualifier [varchar](50) '@shipmentQualifier',
totalEquipment [varchar](50) '@totalEquipment',
transactionSetPurposeCode [varchar](50) '@transactionSetPurposeCode',
applicationType [varchar](50) '@applicationType',
referenceIdentification1 [varchar](50) '@referenceIdentification1',
referenceIdentificationQualifier [varchar](50) '@referenceIdentificationQualifier',
dateQualifier1 [varchar](50) '@dateQualifier1',
[date3] [varchar](50) '@date3',
timeQualifier1 [varchar](50) '@timeQualifier1',
[time3] [varchar](50) '@time3',
noteReferenceCode [varchar](50) '@noteReferenceCode',
[description] [varchar](50) '@description',
stopSequenceNumber [varchar](50) '@stopSequenceNumber',
stopReasonCode [varchar](50) '@stopReasonCode',
weight1 [varchar](50) '@weight1',
weightUnitCode2 [varchar](50) '@weightUnitCode2',
numberofUnitsShipped [varchar](50) '@numberofUnitsShipped',
unitorBasisforMeasurementCode [varchar](50) '@unitorBasisforMeasurementCode',
volume3 [varchar](50) '@volume3',
volumeUnitQualifier4 [varchar](50) '@volumeUnitQualifier4',
dateQualifier2 [varchar](50) '@dateQualifier2',
[date1] [varchar](50) '@date1',
timeQualifier2 [varchar](50) '@timeQualifier2',
[time] [varchar](50) '@time',
timeCode [varchar](50) '@timeCode',
quantityofPalletsShipped [varchar](50) '@quantityofPalletsShipped',
weight2 [varchar](50) '@weight2',
noteReferenceCode1 [varchar](50) '@noteReferenceCode1',
description1 [varchar](50) '@description1',
entityIdentifierCode [varchar](50) '@entityIdentifierCode',
name [varchar](50) '@name',
identificationCodeQualifier [varchar](50) '@identificationCodeQualifier',
identificationCode [varchar](50) '@identificationCode',
addressInformation [varchar](50) '@addressInformation',
cityName [varchar](50) '@cityName',
stateorProvinceCode [varchar](50) '@stateorProvinceCode',
postalCode [varchar](50) '@postalCode',
countryCode [varchar](50) '@countryCode',
contactFunctionCode1 [varchar](50) '@contactFunctionCode1',
name2 [varchar](50) '@name2',
communicationNumberQualifier [varchar](50) '@communicationNumberQualifier',
communicationNumber [varchar](50) '@communicationNumber',
referenceIdentification2 [varchar](50) '@referenceIdentification2',
purchaseOrderNumber [varchar](50) '@purchaseOrderNumber',
packagingFormCode [varchar](50) '@packagingFormCode',
quantity [varchar](50) '@quantity',
weightUnitCode3 [varchar](50) '@weightUnitCode3',
weight3 [varchar](50) '@weight3',
volumeUnitQualifier [varchar](50) '@volumeUnitQualifier',
volume [varchar](50) '@volume',
ladingLineItemNumber [varchar](50) '@ladingLineItemNumber',
commodityCode [varchar](50) '@commodityCode',
commodityCodeQualifier [varchar](50) '@commodityCodeQualifier',
weightQualifier1 [varchar](50) '@weightQualifier1',
commodityCodeQualifier1 [varchar](50) '@commodityCodeQualifier1',
weight4 [varchar](50) '@weight4',
ladingQuantity7 [varchar](50) '@ladingQuantity7',
ladingQuantity2 [varchar](50) '@ladingQuantity2',
volumeUnitQualifier1 [varchar](50) '@volumeUnitQualifier1',
volume1 [varchar](50) '@volume1',
contactFunctionCode [varchar](50) '@contactFunctionCode',
name1 [varchar](50) '@name1',
referenceIdentification3 [varchar](50) '@referenceIdentification3',
referenceIdentificationQualifier1 [varchar](50) '@referenceIdentificationQualifier1',
weight5 [varchar](50) '@weight5',
weightQualifier [varchar](50) '@weightQualifier',
freightRate [varchar](50) '@freightRate',
amountCharged [varchar](50) '@amountCharged',
advances [varchar](50) '@advances',
prepaidAmount [varchar](50) '@prepaidAmount',
volume2 [varchar](50) '@volume2',
volumeUnitQualifier2 [varchar](50) '@volumeUnitQualifier2',
ladingQuantity4 [varchar](50) '@ladingQuantity4',
weightUnitCode4 [varchar](50) '@weightUnitCode4',
declaredValue [varchar](50) '@declaredValue',
rateValueQualifier2 [varchar](50) '@rateValueQualifier2',
numberofIncludedSegments [varchar](50) '@numberofIncludedSegments',
transactionSetControlNumber1 [varchar](50) '@transactionSetControlNumber1',
numberofTransactionSetsIncluded [varchar](50) '@numberofTransactionSetsIncluded',
groupControlNumber1 [varchar](50) '@groupControlNumber1',
numberofIncludedFunctionalGroups [varchar](50) '@numberofIncludedFunctionalGroups',
interchangeControlNumber [varchar](50) '@interchangeControlNumber'
)
This is how my result should look like when I don't want to use multiple cross apply but I am getting the same error
| EDIVERSION | EDIMESSAGECODE | EDIMESSAGEFUNCTION | TESTINDICATOR | ISACONTROLNUMBER | GSCONTROLNUMBER | SENDERID | RECEIVERQUALIFIER | RECEIVERID | authorizationInformationQualifier | authorizationInformation | securityInformationQualifier | securityInformation | interchangeIDQualifier | interchangeSenderID | interchangeIDQualifier2 | interchangeReceiverID | interchangeTime | repetitionSeparator | interchangeControlVersionNumber | interchangeControlNumber | acknowledgmentRequested | interchangeUsageIndicator | componentElementSeparator | functionalIdentifierCode | applicationSendersCode | date | time | groupControlNumber | responsibleAgencyCode | versionReleaseIndustryIdentifierCode | transactionSetIdentifierCode | transactionSetControlNumber | standardCarrierAlphaCode | shipmentIdentificationNumber | weightUnitCode | shipmentMethodofPayment | shipmentQualifier | totalEquipment | transactionSetPurposeCode | applicationType | referenceIdentification | referenceIdentificationQualifier | dateQualifier | date | timeQualifier | time | noteReferenceCode | description | stopSequenceNumber | stopReasonCode | weight | weightUnitCode | numberofUnitsShipped | unitorBasisforMeasurementCode | volume | volumeUnitQualifier | dateQualifier | date | timeQualifier | time | timeCode | quantityofPalletsShipped | weight | noteReferenceCode | description | entityIdentifierCode | name | identificationCodeQualifier | identificationCode | addressInformation | cityName | stateorProvinceCode | postalCode | countryCode | contactFunctionCode | name | communicationNumberQualifier | communicationNumber | referenceIdentification | purchaseOrderNumber | packagingFormCode | quantity | weightUnitCode | weight | volumeUnitQualifier | volume | ladingLineItemNumber | commodityCode | commodityCodeQualifier | weightQualifier | commodityCodeQualifier | weight | ladingQuantity | ladingQuantity2 | volumeUnitQualifier | volume | contactFunctionCode | name | referenceIdentification | referenceIdentificationQualifier | weight | weightQualifier | freightRate | amountCharged | advances | prepaidAmount | volume | volumeUnitQualifier | ladingQuantity | weightUnitCode | declaredValue | rateValueQualifier2 | numberofIncludedSegments | transactionSetControlNumber | numberofTransactionSetsIncluded | groupControlNumber | numberofIncludedFunctionalGroups | interchangeControlNumber |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 5010 | 204 | SM | P | 19708 | 19708 | 14 | 02 | C020C | 00 | 00 | 14 | NEWEFRIL00 | 02 | C020C | 2022-05-06T12:04:00 | U | 00401 | 19708 | 0 | P | :------------------------ | SM | 006922827NWL1 | 2022-05-06T12:04:00 | 12:04:00 | 19708 | X | 004010 | 204 | 19708 | XPOG | 64355525 | L | PP | L | 0.0 | 01 | LT | Ryder | ZZ | 64 | 2022-05-05T14:20:00 | 1 | 14:20:00 | TRA | CFA 509-575-5500 transportation use up to date routing guide | 1 | LD | 43069.2 | L | 0 | PC | 2554.92 | E | 64 | 2022-05-05T14:20:00 | 1 | 14:20:00 | ET | 56 | 0.0 | TRA | CFA 509-575-5500 transportation use up to date routing guide | SF | FISHERS | 93 | 2821 | 200 GRANT WAY | FISHERS | IN | 46037 | USA | SH | CONTACT CONTACT | TE | 123-456-7890 | 2219274061 | Z45LQ | PC | 0 | L | 43069.2 | C | 2554.92 | 0.0 | MISC | N | G | L | 43069.2 | 0.0 | 0.0 | C | 2554.92 | ZZ | ZZ | Z45LQ | DJ | 0 | G | 0 | 0 | 0.0 | 0.0 | 0 | E | 0 | L | 0 | PL | 32 | 19708 | 1 | 19708 | 1 | 19708 | ||
| 5010 | 204 | SM | P | 19708 | 19708 | 14 | 02 | C020C | 00 | 00 | 14 | NEWEFRIL00 | 02 | C020C | 2022-05-06T12:04:00 | U | 00401 | 19708 | 0 | P | :------------------------ | SM | 006922827NWL1 | 2022-05-06T12:04:00 | 12:04:00 | 19708 | X | 004010 | 204 | 19708 | XPOG | 64355525 | L | PP | L | 0.0 | 01 | LT | Ryder | ZZ | 64 | 2022-05-05T14:20:00 | 1 | 14:20:00 | TRA | CFA 509-575-5500 transportation use up to date routing guide | 1 | LD | 43069.2 | L | 0 | PC | 2554.92 | E | 64 | 2022-05-05T14:20:00 | 1 | 14:20:00 | ET | 56 | 0.0 | TRA | CFA 509-575-5500 transportation use up to date routing guide | SF | FISHERS | 93 | 2821 | 200 GRANT WAY | MOXEE CITY | WA | 98936 | USA | SH | CONTACT CONTACT | TE | 123-456-7890 | 2219274061 | Z45LQ | PC | 0 | L | 43069.2 | C | 2554.92 | 0.0 | MISC | N | G | L | 43069.2 | 0.0 | 0.0 | C | 2554.92 | ZZ | ZZ | Z45LQ | DJ | 0 | G | 0 | 0 | 0.0 | 0.0 | 0 | E | 0 | L | 0 | PL | 32 | 19708 | 1 | 19708 | 1 | 19708 | ||
| 5010 | 204 | SM | P | 19708 | 19708 | 14 | 02 | C020C | 00 | 00 | 14 | NEWEFRIL00 | 02 | C020C | 2022-05-06T12:04:00 | U | 00401 | 19708 | 0 | P | :------------------------ | SM | 006922827NWL1 | 2022-05-06T12:04:00 | 12:04:00 | 19708 | X | 004010 | 204 | 19708 | XPOG | 64355525 | L | PP | L | 0.0 | 01 | LT | Ryder | ZZ | 64 | 2022-05-05T14:20:00 | 1 | 14:20:00 | TRA | CFA 509-575-5500 transportation use up to date routing guide | 1 | LD | 43069.2 | L | 0 | PC | 2554.92 | E | 64 | 2022-05-05T14:20:00 | 1 | 14:20:00 | ET | 56 | 0.0 | TRA | CFA 509-575-5500 transportation use up to date routing guide | SF | FISHERS | 93 | 2821 | 9999 E 121ST STREET | FISHERS | IN | 46037 | USA | SH | CONTACT CONTACT | TE | 123-456-7890 | 2219274061 | Z45LQ | PC | 0 | L | 43069.2 | C | 2554.92 | 0.0 | MISC | N | G | L | 43069.2 | 0.0 | 0.0 | C | 2554.92 | ZZ | ZZ | Z45LQ | DJ | 0 | G | 0 | 0 | 0.0 | 0.0 | 0 | E | 0 | L | 0 | PL | 32 | 19708 | 1 | 19708 | 1 | 19708 |
If there is any others please do suggest.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|