Write a SQL script to convert XML data (in SQL XML data type) to a table.(SQL)

Question:

Write a SQL script to convert XML data (in SQL XML data type) to a table. Each attribute of an element in the XML should be converted to a column in the resulting table.
Example:
if the XML looks like:
...

...
then the script should produce the result as:

Table

-----------------------

attr1 | attr2 |

______|_______|

| |

value1 | value 2 |

______|_______|

Using query() and value() methods:

DECLARE @table_temp TABLE (yourXML XML)
DECLARE @xmlDoc XML

SET @xmlDoc =
(
SELECT XCol
FROM dbo.XTable WHERE ID=1
FOR XML RAW, TYPE
)
INSERT INTO @table_temp
SELECT @xmlDoc

SELECT
--t.yourXML,
--r.c.query('.'),
r.c.value('(//@PersonID)[1]', 'varchar (50)') AS PersonID,
r.c.value('(//@LastName)[1]', 'varchar(50)') AS LastName,
r.c.value('(//@PersonID)[2]', 'varchar (50)') AS PersonID,
r.c.value('(//@LastName)[2]', 'varchar(50)') AS LastName
FROM @table_temp t
CROSS APPLY t.yourXML.nodes('row') as r(c)

Using OpenXML Transact-SQL build in function

DECLARE @idoc int, @doc XML;
SET @doc =(
--You can also load a XML here from the table XML field

);
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- SELECT stmt using OPENXML rowset provider
SELECT *
--flag=1 is attribute centric, 2 is element centric. More info at this link : http://msdn.microsoft.com/en-us/library/ms186918(v=sql.105).aspx
FROM OPENXML (@idoc,'/row/XCol/people/person',1)
WITH (PersonID int,
LastName varchar(10)
);