Populate XML Using Table Data

Declare @XML XML

set @XML = ( Select * from pubs..authors Authors FOR XML AUTO, ELEMENTS )

Select @XML

Populate Table Variable Using XML

DECLARE  @tempTable TABLE ( userId INT, userName NVARCHAR(50), password NVARCHAR(50) )

DECLARE  @xml XML

SET @xml=' <row userId="67" userName="Kenny1" password="1234" /> <row userId="80" userName="Kenny2" password="5678" />'

INSERT INTO @tempTable

SELECT Tbl.Col.value('@userId', 'INT'),

Tbl.Col.value('@userName', 'NVARCHAR(50)'),

Tbl.Col.value('@password', 'NVARCHAR(50)')

FROM @xml.nodes('//row') Tbl(Col)



SELECT * FROM @tempTable