This tip explains how to read an XML attribute from 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 the document number (PYMNT000000000012)
The code looks like this:
--create an xml variable, and then read an xml document into it.
declare @x xml
declare @y as varchar(1000)
select @y = '<Receivable documentNumber="PYMNT000000000012 " documentDate="12/13/2007">'
select @y = @y + ' <Type>PAYMENT</Type>'
select @y = @y + ' <CashType>CHECK</CashType>'
select @y = @y + ' <Description />'
select @y = @y + ' <CheckNumber />'
select @y = @y + ' <Amount>3.00000</Amount>'
select @y = @y + ' <AppliedAmount>3.00000</AppliedAmount>'
select @y = @y + ' <UnappliedAmount>0.00000</UnappliedAmount>'
select @y = @y + ' <Items />'
select @y = @y + '</Receivable>'
select @x = @y
--SQL 2000 CODE
-- Initialize XML handle
DECLARE @hdoc INT
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
-- select the records
SELECT x.documentNumber
FROM OPENXML ( @hdoc, '/Receivable', 1 ) WITH (
documentNumber VARCHAR(20) '@documentNumber'
) AS x
-- Release XML handle
EXEC sp_xml_removedocument @hdoc
--SQL 2005 CODE
SELECT
x.header.value('@documentNumber[1]', 'varchar(20)') AS OrderNumber,
x.header.value('Typex[1]', 'varchar(20)') AS Typex2
FROM @x.nodes('//Receivable') AS x(header)