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

That would generally look likeThere are plenty of examples of the xmltable() usage in the manual and on developerWorks.

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.

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.

Oh, sorry

That is obvious that would help

I will show my code in a few hours time.

Thanks very much

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

How about this:

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.

There's at least one closing parenthesis missing in 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?

(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;
???

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.

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:(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.)

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.

I can't copy because all error messages i have to translate from Polish...

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

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>'