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