How to convert XML format using OPENXML

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 2005 Forums

Transact-SQL (2005)

How to convert XML format using OPENXML

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-06-13 : 06:22:25

Hello All,

How can I convert a XML format to a table using OPENXML method? Below are XML format that I want converted.
<Measure_Detail>
<Detail_Date>2009-06-13T05:41:36.543</Detail_Date>
<BatchId>9CDF34D8-7F1D-43BF-93C7-B4F715ACE449</BatchId>
<DSIO_ENT_ID>1600025168</DSIO_ENT_ID>
<LAST_NAME>Wilson</LAST_NAME>
<EMS_ENT_TYPE>PER</EMS_ENT_TYPE>
<Vendor_Type>EXPERT_WITNESS</Vendor_Type>
<ENT_TABLE_ID>194</ENT_TABLE_ID>
<DTTM_RCD_ADDED>19860826160731</DTTM_RCD_ADDED>
<ADDED_BY_USER>atdisuj</ADDED_BY_USER>
<DTTM_RCD_LAST_UPD>19990613170150</DTTM_RCD_LAST_UPD>
<UPDATED_BY_USER>atdisuj</UPDATED_BY_USER>
</Measure_Detail>
<Measure_Detail>
<Detail_Date>2009-06-13T05:41:36.543</Detail_Date>
<BatchId>A8A5843D-48DC-4C79-89FD-BB1DBE2C64A1</BatchId>
<DSIO_ENT_ID>1600025421</DSIO_ENT_ID>
<LAST_NAME>Smith</LAST_NAME>
<EMS_ENT_TYPE>PER</EMS_ENT_TYPE>
<Vendor_Type>EXPERT_WITNESS</Vendor_Type>
<ENT_TABLE_ID>194</ENT_TABLE_ID>
<DTTM_RCD_ADDED>19810826160812</DTTM_RCD_ADDED>
<ADDED_BY_USER>hscfms</ADDED_BY_USER>
<DTTM_RCD_LAST_UPD>19810826160812</DTTM_RCD_LAST_UPD>
<UPDATED_BY_USER>hscfms</UPDATED_BY_USER>
</Measure_Detail>

Please advice.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-13 : 09:57:56

SELECT g.value('BatchId', 'UNIQUEIDENTIFIER') AS BatchID,
g.value('Detail_Date', 'DATETIME') AS DetailDate
FROM @MyXMLvariable.nodes('/Measure_Detail) AS f(g)

E 12°55'05.63"
N 56°04'39.26"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-13 : 21:16:13

I added a ROOT node to the variable, but these two methods seemed to work after.


declare @xml xml 
set @xml = '
<ROOT>
<Measure_Detail>
<Detail_Date>2009-06-13T05:41:36.543</Detail_Date>
<BatchId>9CDF34D8-7F1D-43BF-93C7-B4F715ACE449</BatchId>
<DSIO_ENT_ID>1600025168</DSIO_ENT_ID>
<LAST_NAME>Wilson</LAST_NAME>
<EMS_ENT_TYPE>PER</EMS_ENT_TYPE>
<Vendor_Type>EXPERT_WITNESS</Vendor_Type>
<ENT_TABLE_ID>194</ENT_TABLE_ID>
<DTTM_RCD_ADDED>19860826160731</DTTM_RCD_ADDED>
<ADDED_BY_USER>atdisuj</ADDED_BY_USER>
<DTTM_RCD_LAST_UPD>19990613170150</DTTM_RCD_LAST_UPD>
<UPDATED_BY_USER>atdisuj</UPDATED_BY_USER>
</Measure_Detail>
<Measure_Detail>
<Detail_Date>2009-06-13T05:41:36.543</Detail_Date>
<BatchId>A8A5843D-48DC-4C79-89FD-BB1DBE2C64A1</BatchId>
<DSIO_ENT_ID>1600025421</DSIO_ENT_ID>
<LAST_NAME>Smith</LAST_NAME>
<EMS_ENT_TYPE>PER</EMS_ENT_TYPE>
<Vendor_Type>EXPERT_WITNESS</Vendor_Type>
<ENT_TABLE_ID>194</ENT_TABLE_ID>
<DTTM_RCD_ADDED>19810826160812</DTTM_RCD_ADDED>
<ADDED_BY_USER>hscfms</ADDED_BY_USER>
<DTTM_RCD_LAST_UPD>19810826160812</DTTM_RCD_LAST_UPD>
<UPDATED_BY_USER>hscfms</UPDATED_BY_USER>
</Measure_Detail>
</ROOT>
'

select g.value('BatchId[1]','UNIQUEIDENTIFIER') AS bATCH
		,g.value('ADDED_BY_USER[1]','VARCHAR(40)') AS AddedBy
FROM @XML.nodes('/ROOT/Measure_Detail') AS f(g)



Declare @hDoc int
exec sp_xml_preparedocument @hDoc output,@xml
SELECT *
FROM   OPENXML (@hDoc, '/ROOT/Measure_Detail', 1) 
WITH ( Last_Name Varchar(50) 'LAST_NAME', 
       Vendor_type NVARCHAR(50) 'Vendor_Type',
		UPDATED_BY_USER VARCHAR(50) 'UPDATED_BY_USER'
	  ) 
exec sp_xml_Removedocument @hDoc

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

pras2007
Posting Yak Master

216 Posts

Posted - 2009-06-15 : 10:55:02

Thanks for the response folks, the sample XML that I provided came from a cell in a table. How would I convert the xml for the cell into a table? The table name is TestDB and the field name is Detailed_Value. Please advice. Thanks.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-16 : 02:01:11

Create table value function passing the XML then cross apply.


Create function dbo.myXMLParsefunction
(@myxml xml
)
returns @MyTable table
(
BatchID uniqueIDentifier
,Addedby varchar(40)
)
as
Begin
Insert Into @MyTable(BatchID,AddedBy)
select g.value('BatchId[1]','UNIQUEIDENTIFIER') AS bATCH
		,g.value('ADDED_BY_USER[1]','VARCHAR(40)') AS AddedBy
FROM @myxml.nodes('/ROOT/Measure_Detail') AS f(g)
end

Then you can use like so


Select b.*
from
testdb a
cross apply
dbo.myXmlParsefunction(a.Detailed_Value) b

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources