Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    May 2011
    Posts
    11

    Unanswered: Problem with PureXML

    Hi,

    I have problem with PureXML. I have two tables, for examle:

    table Document
    id int not null
    invoice xml not null

    table Contractor
    id int not null,
    Name varchar not null
    TaxID varchar not null
    Adress xml null

    There's a invoice in table Document. My job is take from the xml data
    like name, taxid, adress and insert into table Contractor.
    My problem is that I don't know how to mix relational and xml data in
    one insert statement.

    If somebody know I'll be very greatfull

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That would generally look like
    Code:
    insert into contractor (name, taxid, address) 
    select name, taxid, address 
    from document d, xmltable(... passing d.invoice as ...)...
    There are plenty of examples of the xmltable() usage in the manual and on developerWorks.

  3. #3
    Join Date
    May 2011
    Posts
    11
    I'm sorry I haven't explained my problem properly.

    Data like Name, TaxID are relational but must be taken from xml. I can handle that.
    But Adress is of piece of xml ducument stored in the first table. it looks like that

    <Street></Street>
    <Number></Number>
    <Country></Country>


    And there is no node Adress in xml Document, I have to add root <adress> while I'm taking that piece of xml. (Idid it using xquery)

    I have to mix relational and xml in one statement.

    I can take relational data and xml but separetaly. Putting this together in one insert fails.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Since you chose not to show your failing code, I have no way of helping you, so I wish you good luck in your efforts.

  5. #5
    Join Date
    May 2011
    Posts
    11
    Oh, sorry

    That is obvious that would help

    I will show my code in a few hours time.

    Thanks very much

  6. #6
    Join Date
    May 2011
    Posts
    11
    Hi,

    Xml Dokument is like that:

    <Invoice>
    <Type>Purchase</Type>
    <Header>
    <DateOfDocument>20090420</DateOfDocument>
    <DateOfSell>220090420</DateOfSell>
    <DateOfPayment>20090519</DateOfPayment>
    <Currency>PLN</Currency>
    <PaymentMethod>BankName Transfer</PaymentMethod>
    <Number> Z0568/2009</Number>
    </Header>
    <Contractors>
    <Seller>
    <Name>ABC Ltd</Name>
    <Street>New Street</Street>
    <Number>15</Number>
    <Postcode>02-350</Postcode>
    <Town>Somewhere</Town>
    <Country></Country>
    <BankName>XYZ Bank</BankName>
    <AccountNo>3736524794100003514687587</AccountNo>
    <Swift></Swift>
    <TaxID>5253687898</TaxID>
    </Seller>
    <Nabywca>
    <Name>XYZ Sp. z o.o.</Name>
    <Street>Nowa</Street>
    <Number>5</Number>
    <Postcode>00-100</Postcode>
    <Town>Anywhere</Town>
    <Country></Country>
    <BankName>Lloyds</BankName>
    <AccountNo>25102010150000000050307090</AccountNo>
    <Swift></Swift>
    <TaxID>5001002030</TaxID>
    </Nabywca>
    </Contractors>
    <Items>
    <Item>
    <Lp>1</Lp>
    <Name>Office Desk no. 5</Name>
    <Qty>20</Qty>
    <UnitPrice>10.00</UnitPrice>
    <NetPrice>200.00</NetPrice>
    <Tax>22%</Tax>
    <TaxAmount>44.00</TaxAmount>
    <GrossPrice>244.00</GrossPrice>
    </Item>
    <Item>
    <Lp>2</Lp>
    <Name>Office Chair no. 5/7</Name>
    <Qty>15</Qty>
    <UnitPrice>7.00</UnitPrice>
    <NetPrice>105.00</NetPrice>
    <Tax>22%</Tax>
    <TaxAmount>23.10</TaxAmount>
    <GrossPrice>128.10</GrossPrice>
    </Item>
    <TotalAmount>372.10</TotalAmount>
    </Items>
    </Invoice>


    And here is a code


    ;
    with contractor as
    (

    Select B.Name, B.TaxID, B.AccountNo, B.Swift
    from Documents, XMLTABLE('$INVOICE/Invoice/Contractors/Seller'
    COLUMNS
    Name varchar(38) PATH 'Name',
    TaxID char(15) PATH ‘TaxID',
    Account varchar(38) PATH 'AccountNo',
    Swift varchar(15) PATH 'Swift') as B

    )
    Insert into Contractors
    Select * from contractor

    Update Contractors
    set Adress = xquery
    for $n in db2-fn:xmlcolumn("DOCUMENTS.INVOICE")/Invoice/Contractors/Seller
    return <Adress>
    {$n/Street, $n/Number, $n/PostCode, $n/Town, $n/Country}
    </Adress>
    where Contractors.TaxID = XMLCAST(XMLQUERY('$INVOICE/Invoice/Contractors/Seller/TaxID/text()'))

    I'd be very greatful for help

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How about this:
    Code:
    with contractor as
    (	
     Select B.Name, B.TaxID, B.AccountNo, B.Swift, b.address
     from Documents, XMLTABLE('$INVOICE/Invoice/Contractors/Seller'
     COLUMNS
      Name varchar(38) PATH 'Name',
      TaxID char(15) PATH ‘TaxID',
      Account varchar(38) PATH 'AccountNo',
      Swift varchar(15) PATH 'Swift',
      Address XML PATH 
        '<Address>{Street,Number,PostCode,Town,Country}</Address>'
     ) as B
    )
    Insert into Contractors (name, taxid, account, swift, address)
    Select name, taxid, account, swift, address from contractor

  8. #8
    Join Date
    May 2011
    Posts
    11
    THANKS!!!!!

    But i have one more question. Suppose that there're another tables named "Bank"

    Bank
    IdBank int,
    Swift char(15)

    and
    Type
    Idtype int
    Name varchar

    Table Type - type of contractor
    1, buyer
    2, seller

    And I don't takie a swift in the cte you wrote. Table contractor looks now like that
    Contractor
    Name
    TaxID
    Idtype
    Adress
    IdBank
    AccountNo


    So in the insert statement i have to add IdType and IdBank. Something like that didn't work:

    Insert into Contractors (name, taxid, Idtype, address, Idbank, account)
    Select
    (select name from contractor) as Name,
    (select taxid from contractor) as TaxID,
    2 as IdType,
    (select Adress from kon ) as Adress,
    (select IdBank from Bank where Swift = (select swift from Bank) as IdBank,
    (select Account from kon) as Account


    I have never worked with db2 before. i have only expierence with Server Sql and in T-Sql it all worked.

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    There's at least one closing parenthesis missing in
    Quote Originally Posted by Iza_33 View Post
    Insert into Contractors (name, taxid, Idtype, address, Idbank, account)
    Select
    (select name from contractor) as Name,
    (select taxid from contractor) as TaxID,
    2 as IdType,
    (select Adress from kon ) as Adress,
    (select IdBank from Bank where Swift = (select swift from Bank) as IdBank,
    (select Account from kon) as Account
    Moreover, that "select IdBank from Bank where Swift = (select swift from Bank)" looks very suspicious to me: the inner query returns all "swift"s from table Bank, which can only work if that table has exactly 1 row; in that case, the outer part of the query just returns that row, so I don't see the point of this entry.

    In general, be careful that the subselects do not return more than one row; they need to be "scalar subselects".

    What was the error message and/or the SQLCODE that you received for the above query?
    Last edited by Peter.Vanroose; 05-21-11 at 17:17.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    May 2011
    Posts
    11
    (select IdBank from Bank where Swift = (select swift from Bank) as IdBank,

    Of course it's a mistake, i was in hurry while I was writing.... It should be like that:

    (select Idbank from Bank where Swift = (select XMLCAST(XMLQUERY('$INVOICE/Invoice/Contractors/Seller/Swift/text()'))))

    or something like that. anyway i have to compare SWIFT in Invoice with Swift from the table Bank and add IdBank

    Error message:

    "unexpected element "Contractors" after "B"

    SQLCODE=-104, SQLSTATE=42601, DRIVER=3.61.65

    And can you explain why something like that doesn't work:

    declare a integer default 2;
    select a;
    ???
    Last edited by Iza_33; 05-22-11 at 03:58.

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Iza_33 View Post
    "unexpected element "Contractors" after "B"
    SQLCODE=-104, SQLSTATE=42601, DRIVER=3.61.65
    Apparently, there's somewhere an occurrence of "B" followed by "Contractors" which is syntactically not allowed.
    Don't see it in your written-out code, though, so I cannot help you out here.

    Quote Originally Posted by Iza_33 View Post
    And can you explain why something like that doesn't work:
    declare a integer default 2;
    select a;
    ???
    Sure: according to the SQL standard, the "SELECT" statement must have a FROM clause.
    "Selecting" is always from a table, otherwise said.

    So, you could e.g. say
    SELECT a FROM sysibm.sysdummy1
    (or from any other table with a single row).

    On the other hand, the "VALUES" statement does not have a FROM clause, and is meant for precisely your situation:
    Code:
    DECLARE a INT DEFAULT 2;
    VALUES (a);
    (There are indeed some database systems, like SQL Server or MySQL, which allow SELECT without FROM, but DB2 does not support this non-standard syntax.)
    Last edited by Peter.Vanroose; 05-22-11 at 07:31.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... i was in hurry while I was writing....
    If you were in hurry, why didn't you copy and paste your code and error messages?
    It is definitly qucker than typing some codes in the message area in this forum.

  13. #13
    Join Date
    May 2011
    Posts
    11
    I can't copy because all error messages i have to translate from Polish...

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can copy your source SQL code.
    And, I don't care Polish text, because I want to see exact values of token, text, token-list, so on, like in the following...

    SQL0104N An unexpected token token was found following text. Expected tokens may include: token-list.

  15. #15
    Join Date
    May 2011
    Posts
    11
    Well,

    i have reached some progress

    I bulit a select statement that gives me all information I need (there's no cte any more) but I can't do a insert. Information is like that "xml is not well-formed with one main element" i guess there is no root. How can I add it. Here is a code:

    INSERT INTO Kontrahenci(Nazwa, idtypu, NIP, DalszeDane, IdBank,KontoBank)
    Select B.Nazwa, B.IdTypu, B.NIP, B.DalszeDane, Bank.IdBank as IdBank, B.Konto
    from TEST, XMLTABLE('$FAKTURA/Faktura/Kontrahenci/Sprzedawca'
    COLUMNS
    Nazwa varchar(38) PATH 'Nazwa',
    IdTypu integer PATH '../../IdTypu',
    NIP char(15) PATH 'Wartość',
    DalszeDane XML PATH
    'for $n in db2-fn:xmlcolumn("TEST.FAKTURA")/Faktura/Kontrahenci/Sprzedawca
    return<DalszeDane>{$n//Ulica}{$n//Numer}{$n//Kod}{$n//Miejscowosc}{$n//Kraj}</DalszeDane>',
    S varchar(15) PATH 'Swift',
    Konto varchar(38) PATH 'NrKonta') as B
    inner join Bank on Bank.Swift = B.S

    Probably problem is this instrution:

    'for $n in db2-fn:xmlcolumn("TEST.FAKTURA")/Faktura/Kontrahenci/Sprzedawca
    return<DalszeDane>{$n//Ulica}{$n//Numer}{$n//Kod}{$n//Miejscowosc}{$n//Kraj}</DalszeDane>'

Posting Permissions

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