Problem

Given a table of data in XML format, write a SQL query that returns the data in a tabular format.
Example input:
<?xml version="1.0" encoding="UTF-8"?>
<data>
  <row>
    <col1>1</col1>
    <col2>2</col2>
    <col3>3</col3>
  </row>
  <row>
    <col1>4</col1>
    <col2>5</col2>
    <col3>6</col3>
  </row>
</data>
Example output:
| col1 | col2 | col3 |
|------|------|------|
| 1    | 2    | 3    |
| 4    | 5    | 6    |

Solution

/*

Solution:

SELECT
    x.value('(/row/col1)[1]', 'int') AS col1,
    x.value('(/row/col2)[1]', 'int') AS col2,
    x.value('(/row/col3)[1]', 'int') AS col3
FROM
    @xml.nodes('/rows/row') AS t(x)

This solution is optimal because it uses the XML data type's built-in methods to parse the XML data.

*/