openxml to build a report

Trying to build a report for XML data stored in a table... Employee.EmployeeUDF has six fields...I need to be able to report on all the data for the table....any way to do this?

So far, I have found a plethora of information that will easily allow me to return one row of the table...like below...but can't get more than that...

DECLARE @.idoc int
DECLARE @.doc varchar(1000)

--The line remarked line below will select the udf xml values from the Employee table
SELECT @.doc = EmployeeUDF FROM Employee

--The following is an example of the getting the udf_date_tamex info from the xml
--SELECT @.doc = ' <udf>
<udf_text_tam>595297022</udf_text_tam>
<udf_date_tamex>2009-12-20</udf_date_tamex>
<udf_text_sher>2547793</udf_text_sher>
<udf_date_sherex>2010-02-15</udf_date_sherex>
<udf_text_helth> 772469 </udf_text_helth>
<udf_date_expriration>2008-04-28</udf_date_expriration>
</udf>'

-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/udf',2)
--specify the fields you wish to return by specifying the tag and datatype
WITH (udf_date_tamex datetime)

EXEC sp_xml_removedocument @.idoc

Thanks....
Jason

If you are using SQL Server 2000, you will have to wrap the above into a cursor that loops over every row.

If you are using SQL Server 2005, you should use the nodes() method in the following way:

select n.value('f_text_tam[1]', 'bigint'), n.value('udf_date_tamex[1]','datetime'), ....
from Employee cross apply EmployeeUDF.nodes('/udf') as E(n)

Best regards
Michael

|||

Thanks Michael. The cursor solution is the one. Should have specified 2000. It also reminds me to request 2005 install tomorrow...

J.