I have a source xml file which I think is fairly complex.  It s an EDIFACT
D96A INVRPT file.

I just need to bring the data within into a single table
CREATE TABLE InventoryOnHand (
InventoryDate nvarchar(10) DEFAULT (getdate()),
Barcode nvarchar(50),
Quantity float,
Warehouse nvarchar(50)
)

I've been fiddling with creating a schema to read the data, but I keep
getting various errors.

If anyone wants to help, let me know & I'll email you a sample source file
and my attempts at a schema.  I'm getting desperate - I've spent so many
hours mucking around I'm at the end of my tether, so any assistance is
greatly appreciated!

cheers

Danny

Please send me your files and I'll take a look.

Regards,
Show quoteHide quote

Monica,
Thanks for your offer.  You can you ensure your email address is correct -
I'm getting NDRs.

cheers

Danny
dan***@accolade.com.au



Show quoteHide quote

Monica,

This might help instead

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

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

The table should be populated thus:

070510      841158000029       144
070510      841158000043       166
070510      841158000043          2
etc





Show quoteHide quote

How to output table as XML with elements on separate lines and indented?
modify() Top-level attribute nodes are not supported Error
Can WordML be put in a SQL Server XML column?
Calling SQL Native Web Service failed as unauthorized
MSSQL2k retrive data from xml