SQLTeam.com | Weblogs | Forums

Get value from xml

Does anyone know how to get the value = "Avis" from this XML.

DECLARE @myDoc XML
DECLARE @ProdID VARCHAR(200)

SET @myDoc = '<?xml version="1.0" encoding="UTF-8"?>

<book category="web">   
	<title lang="en"> Kick Start</title>  
	<author>James McGovern</author>     
	<span class="fav-icon fav-store" data-store-name="Avis" data-store-id="9909"> aaaaaa</span> 
</book> 

'

SELECT @myDoc.value('/bookstore/book/span/@class', 'varchar(200)')

notice in the example you gave there is no bookstore root node

SELECT A.r.value('@data-store-name', 'varchar(max)') as val
  FROM @myDoc.nodes('/book/span') AS A(r)
DECLARE @Xml			AS NVARCHAR(4000) =
N'
<book category="web">   
	<title lang="en"> Kick Start</title>  
	<author>James McGovern</author>     
	<span class="fav-icon fav-store" data-store-name="Avis" data-store-id="9909"> aaaaaa</span> 
</book>
'

DECLARE @Handle			AS INT
EXEC sys.sp_xml_preparedocument @Handle OUTPUT, @Xml

SELECT * FROM OPENXML(@Handle, '/book/span') WITH
(
	  [class]				NVARCHAR(50)
	, [data-store-name]		NVARCHAR(50)
	, [data-store-id]		NVARCHAR(50)
) 

EXEC sys.sp_xml_removedocument @Handle