webmasterbeta
New Member
Hi all,
I've been working different methods to parse an XML document and import it's data into SQL. I have it working but seem to be running into a few issues that make me wonder why XML is better than a flat file.
I'm doing this purely in t-sql with OPENXML in sql2000.
Here's my problem:
1. The XML document has multiple elements under the root. I want to be able to retrieve attributes and values from all of those elements, but for some reason, the element value is always NULL when I have to navigate down a level to retrieve it.
2. I have to remove the header info from the root for OPENXML to work. What good does the header info do for me if OPENXML won't work with it in there. it seems I'm better off receiving my xml document without a schema declaration or namespace. That way it less for me to parse out before processing the xml.
Here's my document. I included the header info at the CustodyTicket root that has to be removed for OPENXML to work:
(Sorry for the format. I can't figure out how to post xml correctly.
)
<CustodyTicket xmlns="http://www.api.org/pidXML/v1.0" xmlns
idx="http://www.api.org/pidXML/v1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.api.org/pidXML/v1.0 <!-- m --><a class="postlink" href="http://haineylp/XML/schemas/CustodyTicket-2002-09-24-V1-0.xsd">http://haineylp/XML/schemas/CustodyTick ... 4-V1-0.xsd</a><!-- m -->" pidx:documentIdentifier="" pidx:transactionPurposeIndicator="Add" pidx:version="1.0">
<CustodyTicketProperties>
<CustodyTicketInformation custodyTicketType="Meter" thirdPartyTicketIndicator="No">
<CustodyTicketNumber>1931</CustodyTicketNumber>
<CustodyTicketDateTime>2003-11-02T04:56:36-05:00</CustodyTicketDateTime>
<CustodyTransferStartDateTime>2003-11-02T03:57:00-05:00</CustodyTransferStartDateTime>
<CustodyTransferStopDateTime>2003-11-02T04:43:00-05:00</CustodyTransferStopDateTime>
<RevisionNumber/>
<CustodySupercedeTicketNumber/>
</CustodyTicketInformation>
<PartnerInformation partnerRoleIndicator="Carrier">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">CPL</PartnerIdentifier>
<PartnerIdentifier partnerIdentifierIndicator="DUNSNumber">3887478</PartnerIdentifier>
<PartnerName>Colonial Pipeline Company</PartnerName>
<AddressInformation>
<AddressLine>P.O. Box 18855</AddressLine>
<CityName>Atlanta</CityName>
<StateProvince>GA</StateProvince>
<PostalCode>31126</PostalCode>
</AddressInformation>
</PartnerInformation>
<PartnerInformation partnerRoleIndicator="Shipper">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">SUN</PartnerIdentifier>
<PartnerName>Sunoco, Inc.</PartnerName>
<AddressInformation>
<AddressLine>ATTN: MEG MONAGHAN</AddressLine>
<AddressLine>TEN PENN CENTER - 1801 MARKET STREET</AddressLine>
<CityName>PHILADELPHIA</CityName>
<StateProvince>PA</StateProvince>
<PostalCode>19103-1699</PostalCode>
</AddressInformation>
</PartnerInformation>
<PartnerInformation partnerRoleIndicator="Supplier">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">TPS</PartnerIdentifier>
<PartnerName>Transmontaigne Prod. Serv. Inc.</PartnerName>
</PartnerInformation>
<PartnerInformation partnerRoleIndicator="Consignee">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">TPS</PartnerIdentifier>
<PartnerName>Transmontaigne Prod. Serv. Inc.</PartnerName>
</PartnerInformation>
<PartnerInformation partnerRoleIndicator="Tankage">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">THL</PartnerIdentifier>
<PartnerName>Transmontaigne tankage on CPC sys former HSS tankage</PartnerName>
</PartnerInformation>
<CustodyTransferInformation>
<FromPartner>
<PartnerInformation partnerRoleIndicator="Carrier">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">CPL</PartnerIdentifier>
<PartnerIdentifier partnerIdentifierIndicator="DUNSNumber">3887478</PartnerIdentifier>
<PartnerName>Colonial Pipeline Company</PartnerName>
<AddressInformation>
<AddressLine>P.O. Box 18855</AddressLine>
<CityName>Atlanta</CityName>
<StateProvince>GA</StateProvince>
<PostalCode>31126</PostalCode>
</AddressInformation>
</PartnerInformation>
</FromPartner>
<ToPartner>
<PartnerInformation partnerRoleIndicator="Consignee">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">TPS</PartnerIdentifier>
<PartnerName>Transmontaigne Prod. Serv. Inc.</PartnerName>
</PartnerInformation>
</ToPartner>
</CustodyTransferInformation>
<CustodyLocationInformation>
<CustodyLocationIdentifier custodyLocationIdentifierIndicator="AssignedByCarrier">RDD</CustodyLocationIdentifier>
<CustodyLocationDescription>RICHMOND</CustodyLocationDescription>
</CustodyLocationInformation>
<ModeOfTransportation>
<TransportMethodCode>Pipeline</TransportMethodCode>
<TransportEvent>Delivery</TransportEvent>
<TransportContainer>Tank</TransportContainer>
</ModeOfTransportation>
<ReferenceInformation referenceInformationIndicator="BatchNumber">
<ReferenceNumber>CPL-SUN-M3-296</ReferenceNumber>
<Description>Carrier Batch Code</Description>
</ReferenceInformation>
<ReferenceInformation referenceInformationIndicator="Other">
<ReferenceNumber>M</ReferenceNumber>
<Description>Carrier Ticket Type</Description>
</ReferenceInformation>
<Comment>METER TICKET</Comment>
</CustodyTicketProperties>
<CustodyTicketDetails>
<CustodyTicketLineItem>
<LineItemNumber>1</LineItemNumber>
<LineItemInformation>
<LineItemIdentifier identifierIndicator="AssignedByCarrier">M3</LineItemIdentifier>
<LineItemName>REG-NONOXY-M3</LineItemName>
<LineItemDescription>Conventional, Regular 87, 11.5 RVP, Non-OXY, Fungible</LineItemDescription>
</LineItemInformation>
<LineItemNetQuantity>
<Quantity>4999</Quantity>
<UnitOfMeasureCode>BR</UnitOfMeasureCode>
</LineItemNetQuantity>
<LineItemGrossQuantity>
<Quantity>4995</Quantity>
<UnitOfMeasureCode>BR</UnitOfMeasureCode>
</LineItemGrossQuantity>
<PipelineLineNumber>27</PipelineLineNumber>
<LineItemTankNumber>0</LineItemTankNumber>
<LineItemMeasures>
<SampleMeasures>
<APIGravity>61.1</APIGravity>
<CompositeFactor>1.0009</CompositeFactor>
</SampleMeasures>
<TankMeasures>
<TankOpenMeasures>
<TankDateTime>2003-11-02T03:57:00-05:00</TankDateTime>
<GaugeQuantity>
<Quantity>0</Quantity>
<UnitOfMeasureCode>BR</UnitOfMeasureCode>
</GaugeQuantity>
</TankOpenMeasures>
<TankCloseMeasures>
<TankDateTime>2003-11-02T04:43:00-05:00</TankDateTime>
<GaugeReadingMeasure>
<Measure>68.375</Measure>
<UnitOfMeasureCode>IN</UnitOfMeasureCode>
</GaugeReadingMeasure>
<GaugeQuantity>
<Quantity>0</Quantity>
<UnitOfMeasureCode>BR</UnitOfMeasureCode>
</GaugeQuantity>
</TankCloseMeasures>
</TankMeasures>
<Meter>
<MeterNumber>1</MeterNumber>
<MeterQuantity>
<Quantity>0</Quantity>
<UnitOfMeasureCode>BR</UnitOfMeasureCode>
</MeterQuantity>
<MeterFactor>1.0078</MeterFactor>
<MeterDistributionPercent>100</MeterDistributionPercent>
<ProverReport>42</ProverReport>
</Meter>
<Meter>
<MeterNumber>2</MeterNumber>
<MeterQuantity>
<Quantity>0</Quantity>
<UnitOfMeasureCode>BR</UnitOfMeasureCode>
</MeterQuantity>
<MeterFactor>1.0047</MeterFactor>
<ProverReport>39</ProverReport>
</Meter>
</LineItemMeasures>
<Comment>This product does not meet the requirements for reformulated gasoline and may not be used in any reformulated gasoline covered area. Base gasoline - Not for sale to the ultimate consumer </Comment>
</CustodyTicketLineItem>
</CustodyTicketDetails>
<CustodyTicketSummary>
<TotalLineItems>1</TotalLineItems>
</CustodyTicketSummary>
</CustodyTicket>
Here's what I run
EXEC SP_XML_PREPAREDOCUMENT @HDOC OUTPUT, @XMLTEXT, 'http://www.api.org/pidXML/v1.0'
SELECT * FROM OPENXML(@HDOC,'/CustodyTicket/CustodyTicketProperties',3)
with
(CustodyTicketNumber int 'CustodyTicketInformation/@CustodyTicketNumber' ,
custodyTicketType char(9) 'CustodyTicketInformation/@custodyTicketType',
thirdPartyTicketIndicator char (9) 'CustodyTicketInformation/@thirdPartyTicketIndicator',
partnerRoleIndicator char (9) 'PartnerInformation/@partnerRoleIndicator',
PartnerIdentifier char(20) 'PartnerInformation/PartnerIdentifier/@partnerIdentifierIndicator')
The line in red is my problem. It returns null but it should return '1931', yet @custodyTicketType returns 'Meter' correctly.
Any idea as to why?
Or perhaps there is a better to import this doucment into SQLServer?
I've been working different methods to parse an XML document and import it's data into SQL. I have it working but seem to be running into a few issues that make me wonder why XML is better than a flat file.
I'm doing this purely in t-sql with OPENXML in sql2000.
Here's my problem:
1. The XML document has multiple elements under the root. I want to be able to retrieve attributes and values from all of those elements, but for some reason, the element value is always NULL when I have to navigate down a level to retrieve it. 2. I have to remove the header info from the root for OPENXML to work. What good does the header info do for me if OPENXML won't work with it in there. it seems I'm better off receiving my xml document without a schema declaration or namespace. That way it less for me to parse out before processing the xml.Here's my document. I included the header info at the CustodyTicket root that has to be removed for OPENXML to work:
(Sorry for the format. I can't figure out how to post xml correctly.
)<CustodyTicket xmlns="http://www.api.org/pidXML/v1.0" xmlns
idx="http://www.api.org/pidXML/v1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.api.org/pidXML/v1.0 <!-- m --><a class="postlink" href="http://haineylp/XML/schemas/CustodyTicket-2002-09-24-V1-0.xsd">http://haineylp/XML/schemas/CustodyTick ... 4-V1-0.xsd</a><!-- m -->" pidx:documentIdentifier="" pidx:transactionPurposeIndicator="Add" pidx:version="1.0"><CustodyTicketProperties>
<CustodyTicketInformation custodyTicketType="Meter" thirdPartyTicketIndicator="No">
<CustodyTicketNumber>1931</CustodyTicketNumber>
<CustodyTicketDateTime>2003-11-02T04:56:36-05:00</CustodyTicketDateTime>
<CustodyTransferStartDateTime>2003-11-02T03:57:00-05:00</CustodyTransferStartDateTime>
<CustodyTransferStopDateTime>2003-11-02T04:43:00-05:00</CustodyTransferStopDateTime>
<RevisionNumber/>
<CustodySupercedeTicketNumber/>
</CustodyTicketInformation>
<PartnerInformation partnerRoleIndicator="Carrier">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">CPL</PartnerIdentifier>
<PartnerIdentifier partnerIdentifierIndicator="DUNSNumber">3887478</PartnerIdentifier>
<PartnerName>Colonial Pipeline Company</PartnerName>
<AddressInformation>
<AddressLine>P.O. Box 18855</AddressLine>
<CityName>Atlanta</CityName>
<StateProvince>GA</StateProvince>
<PostalCode>31126</PostalCode>
</AddressInformation>
</PartnerInformation>
<PartnerInformation partnerRoleIndicator="Shipper">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">SUN</PartnerIdentifier>
<PartnerName>Sunoco, Inc.</PartnerName>
<AddressInformation>
<AddressLine>ATTN: MEG MONAGHAN</AddressLine>
<AddressLine>TEN PENN CENTER - 1801 MARKET STREET</AddressLine>
<CityName>PHILADELPHIA</CityName>
<StateProvince>PA</StateProvince>
<PostalCode>19103-1699</PostalCode>
</AddressInformation>
</PartnerInformation>
<PartnerInformation partnerRoleIndicator="Supplier">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">TPS</PartnerIdentifier>
<PartnerName>Transmontaigne Prod. Serv. Inc.</PartnerName>
</PartnerInformation>
<PartnerInformation partnerRoleIndicator="Consignee">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">TPS</PartnerIdentifier>
<PartnerName>Transmontaigne Prod. Serv. Inc.</PartnerName>
</PartnerInformation>
<PartnerInformation partnerRoleIndicator="Tankage">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">THL</PartnerIdentifier>
<PartnerName>Transmontaigne tankage on CPC sys former HSS tankage</PartnerName>
</PartnerInformation>
<CustodyTransferInformation>
<FromPartner>
<PartnerInformation partnerRoleIndicator="Carrier">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">CPL</PartnerIdentifier>
<PartnerIdentifier partnerIdentifierIndicator="DUNSNumber">3887478</PartnerIdentifier>
<PartnerName>Colonial Pipeline Company</PartnerName>
<AddressInformation>
<AddressLine>P.O. Box 18855</AddressLine>
<CityName>Atlanta</CityName>
<StateProvince>GA</StateProvince>
<PostalCode>31126</PostalCode>
</AddressInformation>
</PartnerInformation>
</FromPartner>
<ToPartner>
<PartnerInformation partnerRoleIndicator="Consignee">
<PartnerIdentifier partnerIdentifierIndicator="AssignedByCarrier">TPS</PartnerIdentifier>
<PartnerName>Transmontaigne Prod. Serv. Inc.</PartnerName>
</PartnerInformation>
</ToPartner>
</CustodyTransferInformation>
<CustodyLocationInformation>
<CustodyLocationIdentifier custodyLocationIdentifierIndicator="AssignedByCarrier">RDD</CustodyLocationIdentifier>
<CustodyLocationDescription>RICHMOND</CustodyLocationDescription>
</CustodyLocationInformation>
<ModeOfTransportation>
<TransportMethodCode>Pipeline</TransportMethodCode>
<TransportEvent>Delivery</TransportEvent>
<TransportContainer>Tank</TransportContainer>
</ModeOfTransportation>
<ReferenceInformation referenceInformationIndicator="BatchNumber">
<ReferenceNumber>CPL-SUN-M3-296</ReferenceNumber>
<Description>Carrier Batch Code</Description>
</ReferenceInformation>
<ReferenceInformation referenceInformationIndicator="Other">
<ReferenceNumber>M</ReferenceNumber>
<Description>Carrier Ticket Type</Description>
</ReferenceInformation>
<Comment>METER TICKET</Comment>
</CustodyTicketProperties>
<CustodyTicketDetails>
<CustodyTicketLineItem>
<LineItemNumber>1</LineItemNumber>
<LineItemInformation>
<LineItemIdentifier identifierIndicator="AssignedByCarrier">M3</LineItemIdentifier>
<LineItemName>REG-NONOXY-M3</LineItemName>
<LineItemDescription>Conventional, Regular 87, 11.5 RVP, Non-OXY, Fungible</LineItemDescription>
</LineItemInformation>
<LineItemNetQuantity>
<Quantity>4999</Quantity>
<UnitOfMeasureCode>BR</UnitOfMeasureCode>
</LineItemNetQuantity>
<LineItemGrossQuantity>
<Quantity>4995</Quantity>
<UnitOfMeasureCode>BR</UnitOfMeasureCode>
</LineItemGrossQuantity>
<PipelineLineNumber>27</PipelineLineNumber>
<LineItemTankNumber>0</LineItemTankNumber>
<LineItemMeasures>
<SampleMeasures>
<APIGravity>61.1</APIGravity>
<CompositeFactor>1.0009</CompositeFactor>
</SampleMeasures>
<TankMeasures>
<TankOpenMeasures>
<TankDateTime>2003-11-02T03:57:00-05:00</TankDateTime>
<GaugeQuantity>
<Quantity>0</Quantity>
<UnitOfMeasureCode>BR</UnitOfMeasureCode>
</GaugeQuantity>
</TankOpenMeasures>
<TankCloseMeasures>
<TankDateTime>2003-11-02T04:43:00-05:00</TankDateTime>
<GaugeReadingMeasure>
<Measure>68.375</Measure>
<UnitOfMeasureCode>IN</UnitOfMeasureCode>
</GaugeReadingMeasure>
<GaugeQuantity>
<Quantity>0</Quantity>
<UnitOfMeasureCode>BR</UnitOfMeasureCode>
</GaugeQuantity>
</TankCloseMeasures>
</TankMeasures>
<Meter>
<MeterNumber>1</MeterNumber>
<MeterQuantity>
<Quantity>0</Quantity>
<UnitOfMeasureCode>BR</UnitOfMeasureCode>
</MeterQuantity>
<MeterFactor>1.0078</MeterFactor>
<MeterDistributionPercent>100</MeterDistributionPercent>
<ProverReport>42</ProverReport>
</Meter>
<Meter>
<MeterNumber>2</MeterNumber>
<MeterQuantity>
<Quantity>0</Quantity>
<UnitOfMeasureCode>BR</UnitOfMeasureCode>
</MeterQuantity>
<MeterFactor>1.0047</MeterFactor>
<ProverReport>39</ProverReport>
</Meter>
</LineItemMeasures>
<Comment>This product does not meet the requirements for reformulated gasoline and may not be used in any reformulated gasoline covered area. Base gasoline - Not for sale to the ultimate consumer </Comment>
</CustodyTicketLineItem>
</CustodyTicketDetails>
<CustodyTicketSummary>
<TotalLineItems>1</TotalLineItems>
</CustodyTicketSummary>
</CustodyTicket>
Here's what I run
EXEC SP_XML_PREPAREDOCUMENT @HDOC OUTPUT, @XMLTEXT, 'http://www.api.org/pidXML/v1.0'
SELECT * FROM OPENXML(@HDOC,'/CustodyTicket/CustodyTicketProperties',3)
with
(CustodyTicketNumber int 'CustodyTicketInformation/@CustodyTicketNumber' ,
custodyTicketType char(9) 'CustodyTicketInformation/@custodyTicketType',
thirdPartyTicketIndicator char (9) 'CustodyTicketInformation/@thirdPartyTicketIndicator',
partnerRoleIndicator char (9) 'PartnerInformation/@partnerRoleIndicator',
PartnerIdentifier char(20) 'PartnerInformation/PartnerIdentifier/@partnerIdentifierIndicator')
The line in red is my problem. It returns null but it should return '1931', yet @custodyTicketType returns 'Meter' correctly.
Any idea as to why?
Or perhaps there is a better to import this doucment into SQLServer?