--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...> wrote:

>
> >
> > The entire contents of the XML are exactly as follows:
>
> I just put all of this in a text blob 2.1.2/ charset NONE and with
> SELECT * FROM TABLE WHERE FIELD CONTAINING 'LOS ANGELES';
> SELECT * FROM TABLE WHERE FIELD CONTAINING '631999';
> SELECT * FROM TABLE WHERE FIELD CONTAINING '57.59';
> SELECT * FROM TABLE WHERE FIELD CONTAINING '</dta:FreightCharge';
> SELECT * FROM TABLE WHERE FIELD CONTAINING 'DiscountPercent';
> I can find the record each time.
>
> >
> > <?xml version="1.0" encoding="UTF-16" ?>
> > <dta:CSInvoice xmlns:msg="http://Epicor.com/InternalMessage/1.1"
> > xmlns:dta="http://Epicor.com/SC/UserSchema">
> > <dta:InvcHead>
> > <dta:InvoiceNum>1247960</dta:InvoiceNum>
> > <dta:CSConfirmationNum>631999</dta:CSConfirmationNum>
> > <dta:StrongOrderNum>9872</dta:StrongOrderNum>
> > <dta:AMCPONum>CS114832</dta:AMCPONum>
> > <dta:OrderNum>284591</dta:OrderNum>
> > <dta:InvoiceTypeDesc>Shipment</dta:InvoiceTypeDesc>
> > <dta:CreditMemo>false</dta:CreditMemo>
> > <dta:DebitNote>false</dta:DebitNote>
> > <dta:InvoiceDate>2009-04-16T00:00:00-05:00</dta:InvoiceDate>
> > <dta:ShipDate>2009-04-16T00:00:00-05:00</dta:ShipDate>
> > <dta:DueDate>2009-04-16T00:00:00-05:00</dta:DueDate>
> > <dta:DspSubTotal>698</dta:DspSubTotal>
> > <dta:TaxAmt>57.59</dta:TaxAmt>
> > <dta:InvoiceAmt>755.59</dta:InvoiceAmt>
> > <dta:MiscChargeTotal>NaN</dta:MiscChargeTotal>
> > <dta:FreightCharge>NaN</dta:FreightCharge>
> > <dta:FreightTaxed>false</dta:FreightTaxed>
> > <dta:CustomerName>AMC/LOEWS - WEB PURCHASE</dta:CustomerName>
> > <dta:FOBDescription>BALDWIN PARK</dta:FOBDescription>
> > <dta:TermsCodeDescription>CHARGE CARD</dta:TermsCodeDescription>
> > <dta:InvoiceComment>.LTI PO#: 71024
> >
> > 4/13/09 1Z07AR010310803953</dta:InvoiceComment>
> > <dta:ShipToNum>AMC0239</dta:ShipToNum>
> > <dta:ShipToNumName>Covina 30</dta:ShipToNumName>
> > <dta:ShipViaCodeDescription>UPS GROUND</dta:ShipViaCodeDescription>
> > <dta:PackNum>209431</dta:PackNum>
> > </dta:InvcHead>
> > <dta:InvcLine>
> > <dta:InvoiceLine>1</dta:InvoiceLine>
> > <dta:RevisionNum></dta:RevisionNum>
> > <dta:POLine></dta:POLine>
> > <dta:UnitPrice>698</dta:UnitPrice>
> > <dta:ExtPrice>698</dta:ExtPrice>
> > <dta:DiscountPercent>0</dta:DiscountPercent>
> > <dta:Discount>0</dta:Discount>
> > <dta:OrdBasedPrice>698</dta:OrdBasedPrice>
> > <dta:TotalMiscChrg>0</dta:TotalMiscChrg>
> > <dta:DspLineTax>57.59</dta:DspLineTax>
> > <dta:DspLineTotal>755.59</dta:DspLineTotal>
> > <dta:SellingShipQty>1</dta:SellingShipQty>
> > <dta:OrderUM>EA</dta:OrderUM>
> > <dta:PartNumTrackSerialNum>true</dta:PartNumTrackSerialNum>
> > </dta:InvcLine>
> > <dta:InvcMiscCharge>
> > <dta:InvoiceLine>1</dta:InvoiceLine>
> > <dta:MiscCodeDesc>FREIGHT ABSORB</dta:MiscCodeDesc>
> > <dta:MiscAmt>0</dta:MiscAmt>
> > </dta:InvcMiscCharge>
> > <dta:TaxDtl>
> > <dta:InvcTax>
> > <dta:ReportableAmt>698</dta:ReportableAmt>
> > <dta:TaxableAmt>698</dta:TaxableAmt>
> > <dta:Percent>6.25</dta:Percent>
> > <dta:TaxAmt>43.63</dta:TaxAmt>
> > <dta:TaxDescription>CA STATE TAX</dta:TaxDescription>
> > </dta:InvcTax>
> > <dta:InvcTax>
> > <dta:ReportableAmt>698</dta:ReportableAmt>
> > <dta:TaxableAmt>698</dta:TaxableAmt>
> > <dta:Percent>2</dta:Percent>
> > <dta:TaxAmt>13.96</dta:TaxAmt>
> > <dta:TaxDescription>CA LOS ANGELES COUNTY</dta:TaxDescription>
> > </dta:InvcTax>
> > <dta:LineTax>
> > <dta:InvoiceLine>1</dta:InvoiceLine>
> > <dta:ReportableAmt>698</dta:ReportableAmt>
> > <dta:TaxableAmt>698</dta:TaxableAmt>
> > <dta:Percent>6.25</dta:Percent>
> > <dta:TaxAmt>43.63</dta:TaxAmt>
> > <dta:TaxDescription>CA STATE TAX</dta:TaxDescription>
> > </dta:LineTax>
> > <dta:LineTax>
> > <dta:InvoiceLine>1</dta:InvoiceLine>
> > <dta:ReportableAmt>698</dta:ReportableAmt>
> > <dta:TaxableAmt>698</dta:TaxableAmt>
> > <dta:Percent>2</dta:Percent>
> > <dta:TaxAmt>13.96</dta:TaxAmt>
> > <dta:TaxDescription>CA LOS ANGELES COUNTY</dta:TaxDescription>
> > </dta:LineTax>
> > </dta:TaxDtl>
> > </dta:CSInvoice>
>

I get that it is working for you. Have you tried putting the contents into a binary blob then searching? Have you then tried moving the contents to a text blob from the binary blob via an update statement and then searching?

I can't explain why it works for you and not for me. I can just assure you that it doesn't work for me.

I have a feeling Helen is right. If I wouldn't have started out with this data being saved in a binary blob, this problem would not exist. There are about 250,000 records in this table. I am going to write a program tomorrow that saves the contents of this field to disk, then reloads it into a text type blob. I'll report my results if you guys care or think it helps in any way.