How to return a single XML attribute from an XML document in SQL Server?

Question

I am just trying to get the JobID attribute from some XML using the following script. Is there a better way to do this?

PROCEDURE [dbo].[spProcessJobXML] @XMLPACKET as nvarchar(MAX) --<Root><Job JobID='2'></Root> AS -- Declare XML doc handle declare @docHandle int Declare @jobid nvarchar(3); Declare @parentid int; declare @testInt int; -- Create XML Doc exec sp_xml_preparedocument @docHandle OUTPUT, @XMLPACKET if exists ( SELECT * FROM tempdb.sys.tables WHERE [name] like '#tempTable%' ) DROP TABLE tempdb.#tempTable; SELECT * INTO #tempTable FROM OPENXML (@docHandle, '/Root/Job') Select top 1 @parentid = #tempTable.id from #tempTable where #tempTable.localname like 'JobID'; --select * from #tempTable; Select top 1 @jobid = #tempTable.[text] from #tempTable where #tempTable.parentid = @parentid; SET @testInt = CAST(@jobid AS int) 

thanks

Answer 1

If you are using SQL Server 2005 or later, you can use the XML data type instead.

 declare @testInt int declare @XMLPACKET as xml set @XMLPACKET = '<Root><Job JobID="2"/></Root>' set @testInt = @XMLPACKET.value('(/Root/Job)[1]/@JobID', 'int') 

Answer 2

Add the c clause to your openxml statement.

 SELECT JobID FROM OPENXML (@docHandle, '/Root/Job') with (JobID int '@JobID')