XML Bulk Load is not loading

Question

Post reply

XML Bulk Load is not loading

Danster

SSCommitted

Points: 1632
More actions
May 11, 2007 at 12:26 am

#178502

OK, I'm a pretty experienced SQL DBA, but a real newbie when it comes to XML stuff.

I need to bring in data from an XML file into a single SQL table. I've spent too many hours to finally figure out a schema mapping file which doesn't error but when I run the SQLXMLBulkLoad my table remains empty.

My table is thus:

CREATE TABLE AA_InventoryOnHand (

InventoryDate nvarchar(10),

Barcode nvarchar(50) ,

Quantity float

)

My sample XML file is long, but copying to notepad and saving as V3_INVRPT.XML should help someone to diagnose. Go down to the ============== line

<?xml version="1.0" encoding="UTF-8"?>

<EDIFACT_D96A_INVRPT xmlns="http://holoncorp.com/xml/EDIFACT/D96A/INVRPT" xmlns:v3="http://holoncorp.com/xml/EDIFACT/D96A/INVRPT">

<UNB UNB010_0001_syntaxIndentifier="UNOA" UNB010_0002_syntaxVersion="3" UNB020_0004_senderIdentification="VISA Sydney" UNB020_0007_partnerIdentificationCodeQualifier="ZZ" UNB030_0007_partnerIdentificationCodeQualifier="ZZ" UNB030_0010_recipientIdentification="Barilla" UNB040_0017_dateOfPreparation="070510" UNB040_0019_timeOfPreparation="1017" UNB050_0020_interchangeControlReference="1020"/>

<INVRPT>

<UNH UNH010_0062_referenceNumber="0001" UNH020_0051_controllingAgency="UN" UNH020_0052_versionNumber="D" UNH020_0054_releaseNumber="96A" UNH020_0057_associationAssignedCode="EAN005" UNH020_0065_typeIdentifier="INVRPT"/>

<BGM BGM010_1001_messageName="35" BGM020_1004_messageNumber="1020" BGM030_1225_messageFunction="9"/>

<DTM DTM010_2005_dateTimePeriodQualifier="366" DTM010_2379_dateTimePeriodFormatQualifier="102" DTM010_2380_dateTimePeriod="20070510"/>

<GRP2>

<NAD NAD010_3035_partyQualifier="GY" NAD020_3039_partyIdIdentification="VISA Sydney" NAD020_3055_codeListResponsibleAgency="86"/>

<GRP4>

<CTA CTA010_3139_contactFunctionCode="WH" CTA020_3412_departmentOrEmployeeName="Jim Vikas"/>

<COM COM010_3148_communicationAddressIdentifier="" COM020_3155_communicationAddressQualifier="TE"/>

<COM COM010_3148_communicationAddressIdentifier="" COM020_3155_communicationAddressQualifier="EM"/>

</GRP4>

</GRP2>

<GRP2>

<NAD NAD010_3035_partyQualifier="GM" NAD020_3039_partyIdIdentification="Cantarella" NAD020_3055_codeListResponsibleAgency="86"/>

</GRP2>

<GRP9>

<LIN LIN030_7140_itemNumber="841158000029" LIN030_7143_itemNumberTypeCode="EN"/>

<GRP12>

<INV INV040_4503_inventoryBalanceMethodCode="1"/>

<QTY QTY010_6060_quantity="144" QTY010_6063_quantityQualifier="17" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="170" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253" QTY010_6411_measurementUnitCode="EA"/>

</GRP12>

</GRP9>

<GRP9>

<LIN LIN030_7140_itemNumber="841158000043" LIN030_7143_itemNumberTypeCode="EN"/>

<GRP12>

<INV INV040_4503_inventoryBalanceMethodCode="1"/>

<QTY QTY010_6060_quantity="166" QTY010_6063_quantityQualifier="17" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="2" QTY010_6063_quantityQualifier="170" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253" QTY010_6411_measurementUnitCode="EA"/>

</GRP12>

</GRP9>

<GRP9>

<LIN LIN030_7140_itemNumber="AZZURRA" LIN030_7143_itemNumberTypeCode="EN"/>

<GRP12>

<INV INV040_4503_inventoryBalanceMethodCode="1"/>

<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="30" QTY010_6063_quantityQualifier="170" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253" QTY010_6411_measurementUnitCode="EA"/>

</GRP12>

</GRP9>

<GRP9>

<LIN LIN030_7140_itemNumber="fab" LIN030_7143_itemNumberTypeCode="EN"/>

<GRP12>

<INV INV040_4503_inventoryBalanceMethodCode="1"/>

<QTY QTY010_6060_quantity="10" QTY010_6063_quantityQualifier="17" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="1" QTY010_6063_quantityQualifier="170" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253" QTY010_6411_measurementUnitCode="EA"/>

<GIN GIN010_7405_identityNumberQualifier="BX" GIN020_7402_identityNumberRange="13"/>

</GRP12>

</GRP9>

<GRP9>

<LIN LIN030_7140_itemNumber="fab" LIN030_7143_itemNumberTypeCode="EN"/>

<GRP12>

<INV INV040_4503_inventoryBalanceMethodCode="1"/>

<QTY QTY010_6060_quantity="100" QTY010_6063_quantityQualifier="17" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="20" QTY010_6063_quantityQualifier="170" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253" QTY010_6411_measurementUnitCode="EA"/>

<GIN GIN010_7405_identityNumberQualifier="BX" GIN020_7402_identityNumberRange="12"/>

</GRP12>

</GRP9>

<GRP9>

<LIN LIN030_7140_itemNumber="INSTANT" LIN030_7143_itemNumberTypeCode="EN"/>

<GRP12>

<INV INV040_4503_inventoryBalanceMethodCode="1"/>

<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="30" QTY010_6063_quantityQualifier="170" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253" QTY010_6411_measurementUnitCode="EA"/>

</GRP12>

</GRP9>

<GRP9>

<LIN LIN030_7140_itemNumber="KING OSCAR" LIN030_7143_itemNumberTypeCode="EN"/>

<GRP12>

<INV INV040_4503_inventoryBalanceMethodCode="1"/>

<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="40" QTY010_6063_quantityQualifier="170" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253" QTY010_6411_measurementUnitCode="EA"/>

</GRP12>

</GRP9>

<GRP9>

<LIN LIN030_7140_itemNumber="sard brisling" LIN030_7143_itemNumberTypeCode="EN"/>

<GRP12>

<INV INV040_4503_inventoryBalanceMethodCode="1"/>

<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="30" QTY010_6063_quantityQualifier="170" QTY010_6411_measurementUnitCode="EA"/>

<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253" QTY010_6411_measurementUnitCode="EA"/>

</GRP12>

</GRP9>

<UNT UNT010_0074_numberOfSegments="54" UNT020_0062_referenceNumber="0001"/>

</INVRPT>

<UNZ UNZ010_0020_interchangeControlReference="1020" UNZ010_0036_interchangeControlCount="1"/>

</EDIFACT_D96A_INVRPT>

=======================

And here is the schema file I've written.

<?xml version="1.0" ?>

<Schema xmlns="urn:schemas-microsoft-com:xml-data"

        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"

        xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

   <ElementType name="EDIFACT_D96A_INVRPT" sql:is-constant="1">

     <element type="UNB" />

     <element type="LIN" />

     <element type="QTY" />

   </ElementType>

   <ElementType name="UNB" sql:relation="AA_InventoryOnHand" >

      <AttributeType name="UNB040_0017_dateOfPreparation" dt:type="string" />

      <attribute type="UNB040_0017_dateOfPreparation" sql:field="InventoryDate" />

   </ElementType>

   <ElementType name="LIN" sql:relation="AA_InventoryOnHand" >

      <AttributeType name="LIN030_7140_itemNumber" dt:type="string" />

      <attribute type="LIN030_7140_itemNumber" sql:field="Barcode" />

   </ElementType>

   <ElementType name="QTY" sql:relation="AA_InventoryOnHand" >

      <AttributeType name="QTY010_6060_quantity" dt:type="float" />

      <attribute type="QTY010_6060_quantity" sql:field="Quantity" />

   </ElementType>

</Schema>

==============================

And here's a vbs file I'm running

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")

objBL.ConnectionString = "provider=SQLOLEDB.1;data source=DANNYCVM;database=MyDemoDB;uid=sa;pwd=MysaPassword"

objBL.ErrorLogFile = "c:\error.log"

objBL.Execute "c:\Invmapping.xml", "c:\V3_INVRPT.xml"

Set objBL = Nothing

=============================

It has taken me quite long to get to this stage but I must be so close! If anyone, PLEEEZ can see why I'm getting no data in my table ?

Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

bledu

SSChampion

Points: 12335

May 11, 2007 at 8:41 am

#705839

remove [xmlns="http://holoncorp.com/xml/EDIFACT/D96A/INVRPT" xmlns:v3="http://holoncorp.com/xml/EDIFACT/D96A/INVRPT">]

from your xml file.

when i tested i managed to load but i had nulls in some columns, so you might want to check your schema file

Everything you can imagine is real.

Danster SSCommitted Points: 1632 More actions · Answer 2

FANTASTIC!

I have posted this same query on a few forums & this is the only one to give some real help! BUT now I've removed those bits from my file, I now get data in the table but each attribute is coming is as a separate record.

Ideally I want them on a single line ie

070510 841158000029 144

070510 841158000043 166

070510 841158000043 2

etc

I know I need to fiddle with the schema but each time I make a change, I get errors - the schema above seems to be the only one I can work out which doesn't error. Any help greatly appreciated.

Danny