Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Posts
    11

    Unanswered: Urgent Please!!! XML Import

    Please Help me,

    One of our customer is sending their RFQ's and PO's by xml format. (XML schema Attached)

    i am importing these xml documents to my Access Database, while importing i am facing a problem, where XML document having a two fields (DocAddText, LItemLongText). these two fields are not importing completely, only the last line of the lenthy text is importing to my database. hence i have set these two fields in my database as Memo type.

    Please help me to resolve this problem. i have to finish this project before 31st of this month.

    please consider myselt that, i no nothing about XML.

    Thanks,
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2003
    Posts
    42

    I don't know XML either but....

    I don't know anything about XML but on a brief look at the XML code you attached, I can see the problem but dunno if I have the solution:

    In the tables you are having problems importing, the problem occurs because Access is re-writing over the previously written record....

    In the code for the table (which imports properly): LItemInfo

    <LItemInfo>
    <ItemSerialNo>00020</ItemSerialNo>
    <ItemMaterialNo>SS0056341</ItemMaterialNo>
    <ItemDeliveryDate>01.07.2003</ItemDeliveryDate>
    <ItemQuantity>2.000</ItemQuantity>
    <ItemUoM>Piece</ItemUoM>
    <ItemUnitPrice>.00</ItemUnitPrice>
    <ItemTotalPrice>.00</ItemTotalPrice>
    <ItemCurrency>SAR</ItemCurrency>
    <ItemManfURL>www.sabic.com</ItemManfURL>
    <ItemManfIMG>../bin/images/litem_info_image.jpg</ItemManfIMG>
    <ItemShortDescription>BOND,P1-30021/BC</ItemShortDescription>
    </LItemInfo>

    I can see that the first bit of code <LItemInfo> is imported as the name of the table. The following are imported as the fields in this table.

    In the tables that don't import proplerly eg:

    <SpecialInstructions>
    <DocAddText>DEAR SUPPLIER THE COMMITMENT OF DELIVERING ON TIME IS ONE OF THE MOST</DocAddText>
    <DocAddText>IMPORTANT ISSUES IN SABIC, SO MAKE SURE TO SEND YOUR QUOTATION ON</DocAddText>
    <DocAddText>IT'S DATE.</DocAddText>
    <DocAddText>SABIC PURCHASING STANDARD ORDER TERMS AND CONDITIONS SHALL APPLY TO</DocAddText>
    <DocAddText>THIS ORDER.</DocAddText>
    </SpecialInstructions>

    I can see that the field <DocAddText> is contstantly being over written because Access interprets this as the same field in the table.

    One solution could be to do some editing of the XML documents before you import them to your Access DB. If you write some code that will edit the XML so that it will look more like this:

    <SpecialInstructions>
    <DocAddText>DEAR SUPPLIER THE COMMITMENT OF DELIVERING ON TIME IS ONE OF THE MOST IMPORTANT ISSUES IN SABIC, SO MAKE SURE TO SEND YOUR QUOTATION ON IT'S DATE. SABIC PURCHASING STANDARD ORDER TERMS AND CONDITIONS SHALL APPLY TO THIS ORDER.</DocAddText>
    </SpecialInstructions>

    There's more than likely a common way to do this.

    But could be a starting point in the meantime...

  3. #3
    Join Date
    Apr 2003
    Posts
    42

    View the code layout like this...

    If you look at the code in this layout you may be able to get a clearer idea about what is going on and how Access undertands you XML code.

    (see attachment)

    Hopefully someone else will have a quick fix solution for you.
    Attached Files Attached Files

  4. #4
    Join Date
    May 2003
    Posts
    11
    Thank you for your prompt reply ventelation,

    Yes as you mensioned i realized the problem, that, it is over writing the field value. But still i don't know the way to do some coding before importing. and also when i contact the customer, who is sending this xml document, when i ask is there a any possibility to do some coding before sending this xml to me, they reply at once no.

    So the coding for to get complete field (DocAddText, LItemLongText) values to be done by our side. I don't know how to do this.

    Please help me any body how to get the complete field value to my data base.

    Thanks in advance

  5. #5
    Join Date
    Apr 2003
    Posts
    42

    Use Macros...

    You can use Macros and Word to auto format your XML files.

    I had a play with some and came up with the following:

    (see the files)


    It works. But assumes that the XML file(s) will be of a similar layout.

    It may be another starting point...

    (Hopefully by reading the files u will follow my logic)
    Attached Files Attached Files

  6. #6
    Join Date
    May 2003
    Posts
    11
    Thanks a lot again ventelation

    Your idea is working fine. But still every day we are receiving more then 25 xml files through edi connection. it is more manual work to done before importing.

    in my database i have form to import the xml files clicking a one button and selectio the file from the folder where xml files are saved.

    Please is there any way to automate this in access (with vb coding) itself.

    Thanks a lot for your continues cooperation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •