XML Query Attach appropriate ID

Question

I seem struggle with XML. I am looking to get appropriate ID attached to each row

Declare @User table (id int,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50))
Insert into @User values
(1,'John','Smith','[email protected]'),
(2,'Jane','Doe'  ,'[email protected]')

Declare @XML xml
Set @XML = (Select * from @User for XML RAW)

Select ID    = 1 -- < dummy need actual id
      ,Item  = cast(x.v.query('local-name(.)') as varchar(100))
      ,Value = x.v.value('.','varchar(150)') 
 From  @xml.nodes('//@*') x(v)

My current result is.

ID  Item        Value
1   id          1
1   First_Name  John
1   Last_Name   Smith
1   EMail       [email protected]
1   id          2
1   First_Name  Jane
1   Last_Name   Doe
1   EMail       [email protected]

My Desired result would be.

ID  Item        Value
1   id          1
1   First_Name  John
1   Last_Name   Smith
1   EMail       [email protected]
2   id          2
2   First_Name  Jane
2   Last_Name   Doe
2   EMail       [email protected]

Answer 1

Try it like this:

Btw: You were pretty close!

Declare @User table (id int,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50))
Insert into @User values
(1,'John','Smith','[email protected]'),
(2,'Jane','Doe'  ,'[email protected]')

Declare @XML xml
Set @XML = (Select * from @User for XML RAW)

SELECT @XML;

/*
<row id="1" First_Name="John" Last_Name="Smith" EMail="[email protected]" />
<row id="2" First_Name="Jane" Last_Name="Doe" EMail="[email protected]" />
*/

The first .nodes() will return with all row elements in single rows The CROSS APPLY .nodes(./@*) will do a row based search for all attributes and deliver them as single rows.

Select r.value('@id','int') AS ID
      ,Attr.value('local-name(.)','varchar(max)') AS Item
      ,Attr.value('.','varchar(max)') AS Value
FROM @XML.nodes('/row') AS A(r)
CROSS APPLY A.r.nodes('./@*') AS B(Attr)

Answer 2

Just came across this question. Though answer to this question was already given, thought to answer it with different approach without using xquery.

You can achieve the same result using CROSS APPLY and Table Value Constructor like this -

Declare @User table (id int,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50))
Insert into @User values
(1,'John','Smith','[email protected]'),
(2,'Jane','Doe'  ,'[email protected]')

SELECT r.ID, t.* FROM @User r
CROSS APPLY (
    VALUES ('ID', cast(id as varchar)),
            ('First_Name', First_Name),
            ('Last_Name', Last_Name),
            ('EMail', EMail)
) t(Item, Value)

Result

ID  Item        Value
---------------------
1   ID          1
1   First_Name  John
1   Last_Name   Smith
1   EMail       [email protected]
2   ID          2
2   First_Name  Jane
2   Last_Name   Doe
2   EMail       [email protected]