openxml - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

openxml

Author

Topic

tekJason
Starting Member

1 Post

Posted - 2006-07-24 : 14:18:04

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

22859 Posts

Posted - 2006-07-24 : 16:48:22

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources