Sep
14
Written by:
Steve Gray
9/14/2009 12:10 PM
This was tested against SQL 2005
This tip explains how to query an XML field in sql server. Or, put another way, if we have this data:
<Receivable documentNumber="PYMNT000000000012 " documentDate="12/13/2007">
<Type>PAYMENT</Type>
<CashType>CHECK</CashType>
<Description />
<CheckNumber />
<Amount>3.00000</Amount>
<AppliedAmount>3.00000</AppliedAmount>
<UnappliedAmount>0.00000</UnappliedAmount>
<Items />
</Receivable>
We want to use SQL Server 2005 to retrieve all lines that have the document number ‘PYMNT000000018542’. The code looks like this:
SELECT transmittedDoc.query('/Receivable[@documentNumber="PYMNT18542"]')
as docnbr,
DATALENGTH( transmittedDoc.query('/Receivable[@documentNumber="PYMNT18542"]') ),
*
from _postReceivable
where dtProcessed > '9/14/2009'
and DATALENGTH( transmittedDoc.query('/Receivable[@documentNumber="PYMNT18542"]') ) > 5
SELECT transmittedDoc.query('/Receivable[@documentNumber="PYMNT18542"]') as docnbr,
*
from _postReceivable
where dtProcessed > '9/14/2009'
and convert(varchar(max),
transmittedDoc.query('/Receivable[@documentNumber="PYMNT18542"]') ) > ''
The first query uses the DATALENGTH function, the second converts to varchar and then tests for empty string.
When using DATALENGTH, I was getting a length of 5 on my empty strings for some reason.
As always, I welcome your comments!