Inserting parsed XML into SQL - Null problem

[1Devl0pr]

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:
:confused: 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.
:confused: 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. :rolleyes: )

<CustodyTicket xmlns="http://www.api.org/pidXML/v1.0" xmlns:pidx="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 http://haineylp/XML/schemas/CustodyTicket-2002-09-24-V1-0.xsd" 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?:confused:

[russell]

where does the xml doc come from -- your own company, or an external one? if yours, then a csv or other delimited text file is far superior to xml -- and you should use bulk insert or dts to do the import.

if it's external and u have no control over the format, u have 2 choices that are both probably better than what u r trying to do: 1, use dts and create an activeX script task to parse the xml using the msxmldomdocument object or (2) write a vb (or dot net) compiled app or component to parse and upload the data (msxmldomdocument is probably still the right tool for the job).

[1Devl0pr]

Thanks for the replay. It's external. I don't have any choice over the format.
I'm using t-sql to parse the document because it's the easiest to do since I can't find any help ort samples anywhere on using vb and msmxl objects. I spent a several hours looking too.
If you could direct me to some or help out with the issue I'm having below, I'd appreciate it.
I really hate using DTS unless it's for some quick non-production stuff.
Thanks!

[1Devl0pr]

anyone?

[russell]

http://msdn.microsoft.com/XML/BuildingXML/ProcessXMLwMSXML/default.aspx

http://c2.com/cgi/wiki?VbXml

http://msdn.microsoft.com/XML/XMLCodeSamples/default.aspx

[1Devl0pr]

Thanks so much. I'll give it a look.
Also, if anyone knows my T-SQL issue described earlier puling attributes and element values simultaneously when pointing to a directory one level above(only returns the attribute, not the element), I'd love to hear why that is.
Thanks again!