Subject:Prevent xquery from converting < and > Author:Mike Koporc Date:05 Aug 2008 05:35 PM
I am using a string concat and I want to create the xml tags in the concat. So for example concat("<test>", $row1/test, "</test>"). When I run the XQuery it is converting it to <test>. There is a style sheet being applied to it after that doesn't recognize the escape characters. Any way to turn this off? Thanks!
Subject:Prevent xquery from converting < and > Author:Mike Koporc Date:05 Aug 2008 09:55 PM
The reason I decided to use concat instead of just using the tags normally is because I had to leave open tags in certain places.
This is what I had originally tried, but it is not considered valid. Using the same structure but using concat instead of the xml tags produces the correct output (except that it is converting to < and >). So I'm ok with a way to make the code below valid or turning off the xml conversion from < to <.
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?
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
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
Subject:Prevent xquery from converting < and > Author:Minollo I. Date:06 Aug 2008 09:06 AM
Looks like you are trying to get some positional grouping. Let's try this then (I have assumed an "M" record immediately follows the corresponding "H" record, if present):
declare function local:getRelatedDs($item) {
let $nextItem := $item/following-sibling::*[column.0="H"][1]
for $related in $item/following-sibling::*[column.0="D"]
where if($nextItem) then $related << $nextItem else true()
return $related
};
<MA540> {
for $H in doc('converter:CSV?file:///c:/Documents%20and%20Settings/mkoporc/Desktop/SS2008/810/MA540CSV.20080708')/table/row
(: for $H in doc('c:/test.xml')/table/row :)
where $H/column.0 = "H"
return
<InvoiceLoop>
<Invoice002_Company>{$H/column.1/text()}</Invoice002_Company>
<Invoice003_Vendor-ID>{$H/column.2/text()}</Invoice003_Vendor-ID>
<Invoice004_EDINumber>{$H/column.3/text()}</Invoice004_EDINumber>
<Invoice005_InvoiceNumber>{$H/column.4/text()}</Invoice005_InvoiceNumber>
<Invoice006_Suffix>{$H/column.5/text()}</Invoice006_Suffix>
<Invoice007_RecordType>{$H/column.6/text()}</Invoice007_RecordType>
<Invoice008_TaxCode>{$H/column.7/text()}</Invoice008_TaxCode>
<Invoice011_PONumber>{$H/column.10/text()}</Invoice011_PONumber>
<Invoice019_InvoiceDate>{$H/column.18/text()}</Invoice019_InvoiceDate>
<Invoice023_InvoiceAmount>{$H/column.22/text()}</Invoice023_InvoiceAmount>
<Invoice031_DueDate>{$H/column.30/text()}</Invoice031_DueDate>
<Invoice038_Operator>{$H/column.37/text()}</Invoice038_Operator>
{
let $M := $H/following-sibling::*[1][column.0="M"]
return (
<Invoice122_Misc-AOC-Code>{$M/column.121/text()}</Invoice122_Misc-AOC-Code>,
<Invoice123_Misc-AOC-Amount>{$M/column.122/text()}</Invoice123_Misc-AOC-Amount>
)
}
{
for $D in local:getRelatedDs($H)
return
<InvoiceLineItemLoop>
<Invoice048_Sequence-Number>{$D/column.47/text()}</Invoice048_Sequence-Number>
<Invoice061_Line-Number>{$D/column.60/text()}</Invoice061_Line-Number>
<InvoiceDescription>{$D/column.61/text()}</InvoiceDescription>
<Invoice063_VendorItemIdentifier>{$D/column.62/text()}</Invoice063_VendorItemIdentifier>
<Invoice064_EnteredQuantity>{$D/column.63/text()}</Invoice064_EnteredQuantity>
<Invoice065_EnteredUOM>{$D/column.64/text()}</Invoice065_EnteredUOM>
<Invoice066_UnitCost>{$D/column.65/text()}</Invoice066_UnitCost>
</InvoiceLineItemLoop>
}
</InvoiceLoop>
} </MA540>
We don't provide specific tutorials about them; being standard components of XPath, you will find many discussions on the Web or in books discussing XPath or XQuery.