Hi,

I'm having difficulties with SQLXML and some behind the scenes jiggery pokery it's getting up to. Essentially, I'm using schemas and XPath queries to retrieve XML data from SQL Server 2000. I noticed that one of the queries seemed to be running slower than expected, so I profiled the database to look at the query which SQLXML was generating. The result was:

select
1 as TAG,0 as parent,_Q1.A0 as [txn!1!parentid],_Q1.A1 as [txn!1!customerid],
_Q1.A2 as [txn!1!authorityid],_Q1.A3 as [txn!1!txnid],
CONVERT(nvarchar(4000),_Q1.A4,126) as [txn!1!invnotifydate],
_Q1.A5 as [txn!1!invoiceno] from (select _QB0.parent_id AS A0,
_QB0.customer_id AS A1,_QB0.authority_id AS A2,_QB0.txn_id AS A3,
_QB0.process_date AS A4,_QB0.invoice_no AS A5,_QB0.invoice_no AS _TBILJHOEFA,
_QB0.process_date AS _TBILIHOEFA,_QB0.txn_id AS _TBIHHHOEFA,_QB0.authority_id AS _TBIDFHOEFA,
_QB0.customer_id AS _TBIMEHOEFA,_QB0.parent_id AS _TBIBKHOEFA
from
vw_xml_txn_i_short _QB0) _Q1
WHERE
(CONVERT(nvarchar(4000),_Q1.A4,126) IS NOT NULL AND
(CONVERT(nvarchar(4000),_Q1.A4,126) >= N'2003-08-08T23:00:00') AND
CONVERT(nvarchar(4000),_Q1.A2,126) IS NOT NULL AND
(CONVERT(nvarchar(4000),_Q1.A2,126) = N'U127002'))
for xml explicit, binary base64

For some reason, the XPath query of (@authorityid = 'U127002' and @invnotifydate >= '2003-08-08T23:00:00') is being converted into Unicode comparisons in the WHERE clause, even though the underlying data isn't unicode. Ultimately it means the indexes which I've created to speed up the query aren't being used. The above query takes around 6 seconds - if I remove the unicode conversions it takes under a second.

Short of rewriting this to use explicit FOR XML queries and not schemas/XPath is there any way of addressing this?

Thanks in advance

Paul Armstrong