SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 openxml
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tekJason
Starting Member

1 Posts

Posted - 07/24/2006 :  14:18:04  Show Profile  Reply with Quote
Trying to build a report for XML data stored in a table... Employee.EmployeeUDF has six fields in each row...I need to be able to report on all the data in every row....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

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/24/2006 :  16:48:22  Show Profile  Reply with Quote
There is only one record in your XML ...

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 = '<UDFs>
<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>
<udf>
<udf_text_tam>595297023</udf_text_tam>
<udf_date_tamex>2009-12-21</udf_date_tamex>
<udf_text_sher>2547794</udf_text_sher>
<udf_date_sherex>2010-02-16</udf_date_sherex>
<udf_text_helth> 772470 </udf_text_helth>
<udf_date_expriration>2008-04-29</udf_date_expriration>
</udf>
</UDFs>'


-- 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, '/UDFs/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

will display two records

Kristen

Edited by - Kristen on 07/24/2006 16:48:47
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000