Post reply

XML Bulk Load is not loading

  • 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

  • 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.

  • May 11, 2007 at 10:23 pm

    #706031

    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

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

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