如何从SQL Server中的XML文档返回单个XML属性？

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

我只是尝试使用以下脚本从某些XML获取JobID属性。有一个更好的方法吗？

    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

谢谢

2 个解决方案

#1

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

如果您使用的是SQL Server 2005或更高版本，则可以使用XML数据类型。

declare @testInt int
declare @XMLPACKET as xml 
set @XMLPACKET = '<Root><Job JobID="2"/></Root>'

set @testInt = @XMLPACKET.value('(/Root/Job)[1]/@JobID', 'int')

#2

Add a "with" clause to your openxml statement.

在openxml语句中添加“with”子句。

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

#1