How to select XML data as SQL table in SQl server 2005

How to select XML data as SQL table in SQl server 2005.

we can query xml datatype in sql server to retrive all nodes as SQL table.
xml datatype is new in SQL Server 2005.

if we have XML data like :

declare @UserXML XML

set @UserXML='<Users><User><ID>1</ID><Name>Haresh,Herry</Name><IsLoggedIn>1</IsLoggedIn>
</User><User><ID>2</ID><Name>Paresh,Pariyo,Perry</Name><IsLoggedIn>1</IsLoggedIn>
</User><User><ID>3</ID><Name>Ajay,Sinh,Bapu</Name><IsLoggedIn>1</IsLoggedIn>
</User><User><ID>4</ID><Name>Abhijit</Name></User></Users>'

We can query them as :

SELECT tableName.columnName.value('ID[1]','int')AS ID, tableName.columnName.value('Name[1]','VARCHAR(100)')AS [Name], tableName.columnName.value('IsLoggedIn[1]','bit')AS IsLoggedIn
FROM @UserXML.nodes('//User') tableName(columnName)

after executing above Query output will be:

Figure-1(Output)







Thanks.