SQL Server XQuery on one line?

Question

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:

what well.

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

Sort of:

  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:

Answer 1

You can do:

 Select CONVERT(xml, N'<a key="2"></a>', 1).value('a[1]/@key', 'varchar(8000)') 

Answer 2

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)