4
 DECLARE @myXml XML

 SET @myXml =  CONVERT(xml, '<a key="2"></a>', 1)

 SELECT  s.value('@key', 'VARCHAR(8000)')   AS myKey from   @myXml.nodes('/a')  t(s)

answer :

enter image description here

which is fine.

I want to do it without declaring the @myXml variable.

Something like : 

 SELECT  
    s.value('@key', 'VARCHAR(8000)') AS myKey 
 FROM 
    CONVERT(xml, N'<a key="2"></a>', 1) .nodes('/a')  t(s)

but I get an error :

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Royi Namir
  • 144,742
  • 138
  • 468
  • 792

2 Answers2

2

You can do:

Select CONVERT(xml, N'<a key="2"></a>', 1).value('a[1]/@key', 'varchar(8000)')
Ta01
  • 31,040
  • 13
  • 70
  • 99
  • @Royi Namir: That limits you to one instance of the 'a' tag under the root. If you want more than one instance (if your real XML string is more complex than the one in your example) you'll need a different approach that actually shreds the XML like your first sample did (and mine, below, does). If there _is_ just a single 'a' tag, kd7's approach will certainly be faster. – mwigdahl Jan 09 '12 at 16:40
1

This should work:

;WITH xgen (xdata)
AS
(
    SELECT CONVERT(xml, '<a key="2"></a>', 1) AS xdata
)
select s.value('@key', 'VARCHAR(8000)')   AS myKey 
from xgen
    cross apply xgen.xdata.nodes('/a') t(s)
mwigdahl
  • 16,268
  • 7
  • 50
  • 64