|
|
 next
|
  Subject: Prevent xquery from converting < and >
Author: Minollo I.
Date: 05 Aug 2008 10:36 PM
|
You definitely want to re-arrange the XQuery to make it well formed using open/closed tags. Concat() is meant to work with strings, and it won't help you creating XML output.
I'm not positive I understand precisely what your XQuer is trying to do; but maybe this help?
<MA540>
{
for $row1 in doc('converter:CSV?file:///c:/Documents%20and%20Settings/mkoporc/Desktop/SS2008/810/MA540CSV.20080708')/table/row
let $x := SystemUtils:Count() ,
$last := SystemUtils:GetPrev() ,
$cur := SystemUtils:SetPrev($row1/column.0/text())
return
<InvoiceLoop> {
if($x = 1) then (
<Invoice002_Company>{$row1/column.1/text()}</Invoice002_Company>,
<Invoice003_Vendor-ID>{$row1/column.2/text()}</Invoice003_Vendor-ID>,
<Invoice004_EDINumber>{$row1/column.3/text()}</Invoice004_EDINumber>,
<Invoice005_InvoiceNumber>{$row1/column.4/text()}</Invoice005_InvoiceNumber>,
<Invoice006_Suffix>{$row1/column.5/text()}</Invoice006_Suffix>,
<Invoice007_RecordType>{$row1/column.6/text()}</Invoice007_RecordType>,
<Invoice008_TaxCode>{$row1/column.7/text()}</Invoice008_TaxCode>,
<Invoice011_PONumber>{$row1/column.10/text()}</Invoice011_PONumber>,
<Invoice019_InvoiceDate>{$row1/column.18/text()}</Invoice019_InvoiceDate>,
<Invoice023_InvoiceAmount>{$row1/column.22/text()}</Invoice023_InvoiceAmount>,
<Invoice031_DueDate>{$row1/column.30/text()}</Invoice031_DueDate>,
<Invoice038_Operator>{$row1/column.37/text()}</Invoice038_Operator>
)
else if(compare($cur, "H") = 0 ) then (
<Invoice002_Company>{$row1/column.1/text()}</Invoice002_Company>,
<Invoice003_Vendor-ID>{$row1/column.2/text()}</Invoice003_Vendor-ID>,
<Invoice004_EDINumber>{$row1/column.3/text()}</Invoice004_EDINumber>,
<Invoice005_InvoiceNumber>{$row1/column.4/text()}</Invoice005_InvoiceNumber>,
<Invoice006_Suffix>{$row1/column.5/text()}</Invoice006_Suffix>,
<Invoice007_RecordType>{$row1/column.6/text()}</Invoice007_RecordType>,
<Invoice008_TaxCode>{$row1/column.7/text()}</Invoice008_TaxCode>,
<Invoice011_PONumber>{$row1/column.10/text()}</Invoice011_PONumber>,
<Invoice019_InvoiceDate>{$row1/column.18/text()}</Invoice019_InvoiceDate>,
<Invoice023_InvoiceAmount>{$row1/column.22/text()}</Invoice023_InvoiceAmount>,
<Invoice031_DueDate>{$row1/column.30/text()}</Invoice031_DueDate>,
<Invoice038_Operator>{$row1/column.37/text()}</Invoice038_Operator>
)
else if(compare($cur, "M") = 0) then (
<Invoice122_Misc-AOC-Code>{$row1/column.121/text()}</Invoice122_Misc-AOC-Code>,
<Invoice123_Misc-AOC-Amount>{$row1/column.122/text()}</Invoice123_Misc-AOC-Amount>
)
else if (compare($cur, "D") = 0) then (
<Invoice048_Sequence-Number>{$row1/column.47/text()}</Invoice048_Sequence-Number>,
<Invoice061_Line-Number>{$row1/column.60/text()}</Invoice061_Line-Number>,
<InvoiceDescription>{$row1/column.61/text()}</InvoiceDescription>,
<Invoice063_VendorItemIdentifier>{$row1/column.62/text()}</Invoice063_VendorItemIdentifier>,
<Invoice064_EnteredQuantity>{$row1/column.63/text()}</Invoice064_EnteredQuantity>,
<Invoice065_EnteredUOM>{$row1/column.64/text()}</Invoice065_EnteredUOM>,
<Invoice066_UnitCost>{$row1/column.65/text()}</Invoice066_UnitCost>
)
else ()
} </InvoiceLoop>
}
</MA540>
|
|
next
|
  Subject: Prevent xquery from converting < and >
Author: Mike Koporc
Date: 05 Aug 2008 11:31 PM
|
The problem is that </InvoiceLoop> doesn't necessarily end each row. It might not be closed until the 8th row. For example the input file could look like
H,h1,h2
M,h1,h2,m1
D,h1,h2,d1,d2
D,h1,h2,d1,d2
D,h1,h2,d1,d2
H,h1,h2
M,h1,h2,m1
D,h1,h2,d1,d2
The goal is to avoid all the repetition in the header data and only grab the pieces from each row that are unique and combine them.
So the output would need to look like
<MA540>
<InvoiceLoop>
<H1></H1>
<H2></H2>
<M1></M1>
<InvoiceLineItemLoop>
<D1></D1>
<D2></D2>
</InvoiceLineItemLoop>
<InvoiceLineItemLoop>
<D1></D1>
<D2></D2>
</InvoiceLineItemLoop>
<InvoiceLineItemLoop>
<D1></D1>
<D2></D2>
</InvoiceLineItemLoop>
</InvoiceLoop>
<InvoiceLoop>
<H1></H1>
<H2></H2>
<M1></M1>
<InvoiceLineItemLoop>
<D1></D1>
<D2></D2>
</InvoiceLineItemLoop>
</InvoiceLoop>
</MA540>
Hopefully that help explains what I'm aiming for. Thank you for your help so far.
|
|
|
|
|
top