Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2007
    Posts
    84

    Unanswered: Load XML data to db2 table

    I'm using DB2 9.1 with AIX and Unix. I need to load an XML file to a db2 table F4216. The table has already been created and there is a stored procedure called INSERTXMLINF4216. I'm also including the XML file below.
    I need to know if I can call this SP on the Unix command to run manually?

    Here is the Stored Procedure:

    CREATE PROCEDURE INSERTXMLINF4216 (IN objXML XML)

    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    INSERT INTO F4216
    (
    XDDOCO, --DECIMAL 8
    XDDCTO, --GRAPHIC 2
    XDKCOO, --GRAPHIC 5
    XDLNID, --DECIMAL 6
    XDPLT, --GRAPHIC 18
    XDPAK, --GRAPHIC 18
    XDSCCN, --GRAPHIC 14
    XDSCCQ, --DECIMAL 15
    XDSCUM, --GRAPHIC 2
    XDUPCN, --GRAPHIC 13
    XDUPQT, --DECIMAL 15
    XDUPUM, --GRAPHIC 2
    XDEQTY, --GRAPHIC 5
    XDCNID, --GRAPHIC 20
    XDUSER, --GRAPHIC 10
    XDPID, --GRAPHIC 10
    XDJOBN, --GRAPHIC 10
    XDUPMJ, --DECIMAL 6
    XDTDAY --DECIMAL 6
    )
    --<ContainterCode />
    --<ContainterID />
    --<DateSent>111098</DateSent>
    --<DocumentNumber>8833</DocumentNumber>
    --<LineNumber>0</LineNumber>
    --<OrderCompany>00010</OrderCompany>
    --<OrderType>SO</OrderType>
    --<PackSSCC18>PACKSSCC</PackSSCC18>
    --<ProgramID />
    --<SCC />
    --<SCCQty>0</SCCQty>
    --<SCCUOM />
    --<TareSSCC18>TARESSCC</TareSSCC18>
    --<TimeSent>170107</TimeSent>
    --<UPC>CT</UPC>
    --<UPCQty>1</UPCQty>
    --<UPCUOM>CT</UPCUOM>
    --<UserID>e1rf</UserID>
    --<WorkStation>wkbenchsql</WorkStation>
    SELECT X.*
    FROM XMLTABLE ('$d/F4216/clsF4216Out' passing objXML as "d"
    COLUMNS DocumentNumber DECIMAL(8,0) PATH 'DocumentNumber',
    OrderType VARCHAR(2) PATH 'OrderType',
    OrderCompany VARCHAR(5) PATH 'OrderCompany',
    LineNumber DECIMAL(6,0) PATH 'LineNumber',
    TareSSCC18 VARCHAR(18) PATH 'TareSSCC18',
    PackSSCC18 VARCHAR(18) PATH 'PackSSCC18',
    SCC VARCHAR(14) PATH 'SCC',
    SCCQty DECIMAL(15,0) PATH 'SCCQty',
    SCCUOM VARCHAR(2) PATH 'SCCUOM',
    UPC VARCHAR(13) PATH 'UPC',
    UPCQty DECIMAL(15,0) PATH 'UPCQty',
    UPCUOM VARCHAR(2) PATH 'UPCUOM',
    ContainterCode VARCHAR(5) PATH 'ContainterCode',
    ContainterID VARCHAR(20) PATH 'ContainterID',
    UserID VARCHAR(10) PATH 'UserID',
    ProgramID VARCHAR(10) PATH 'ProgramID',
    WorkStation VARCHAR(10) PATH 'WorkStation',
    DateSent DECIMAL(6,0) PATH 'DateSent',
    TimeSent DECIMAL(6,0) PATH 'TimeSent'
    ) AS X;
    END P1



    Here is the XML file:
    <?xml version="1.0" encoding="utf-16"?>
    <clsF4216Out xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <ContainterCode />
    <ContainterID />
    <DateSent>112121</DateSent>
    <DocumentNumber>385658</DocumentNumber>
    <LineNumber>56000</LineNumber>
    <OrderCompany>00010</OrderCompany>
    <OrderType>SO</OrderType>
    <PackSSCC18>00010000249</PackSSCC18>
    <ProgramID />
    <SCC>0</SCC>
    <SCCQty>0</SCCQty>
    <SCCUOM />
    <TareSSCC18 />
    <TimeSent>122538</TimeSent>
    <UPC />
    <UPCQty>0</UPCQty>
    <UPCUOM />
    <UserID>sulmik</UserID>
    <WorkStation>rfdev</WorkStation>
    </clsF4216Out>



    Now can I call this db2 Stored Procedure for the Unix command line to Insert?
    Something like this? I don't know if I'm on the right track here...I've never done this.

    db2 "call INSERTXMLINF4216
    (
    XMLPARSE(
    DOCUMENT
    <clsF4216Out xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <ContainterCode />
    <ContainterID />
    <DateSent>112121</DateSent>
    <DocumentNumber>385658</DocumentNumber>
    <LineNumber>56000</LineNumber>
    <OrderCompany>00010</OrderCompany>
    <OrderType>SO</OrderType>
    <PackSSCC18>00010000249</PackSSCC18>
    <ProgramID />
    <SCC>0</SCC>
    <SCCQty>0</SCCQty>
    <SCCUOM />
    <TareSSCC18 />
    <TimeSent>122538</TimeSent>
    <UPC />
    <UPCQty>0</UPCQty>
    <UPCUOM />
    <UserID>sulmik</UserID>
    <WorkStation>rfdev</WorkStation>
    </clsF4216Out>
    )"


    Thank you for your help.

    CC

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rockycj View Post

    Now can I call this db2 Stored Procedure for the Unix command line to Insert?
    Sure you can, as soon as you get your quotation marks straight.

Posting Permissions

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