🙂
Alternatively use OPENXML
DECLARE @xmlDocument nvarchar(max)
SET @xmlDocument = N'<table>
<tr>
<td>cell1</td>
<td>cell2</td>
<td>cell3</td>
</tr>
<tr>
<td>cell4</td>
<td>cell5</td>
<td>cell6</td>
</tr>
<tr>
<td>cell7</td>
<td>cell8</td>
<td>cell9</td>
</tr>
</table>';
DECLARE @docHandle int;
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
WITH rs1 AS (
SELECT cast(a.text as varchar(100)) [text]
, row_number() over (partition by b.parentid order by a.parentid) AS colID
, b.parentid AS rowid
FROM OPENXML(@docHandle, N'/table/tr') a
JOIN OPENXML(@docHandle, N'/table/tr') b
ON a.Localname='#text' and a.parentid = b.id
)
SELECT rowid, [1],[2],[3]
FROM rs1
PIVOT (max([text]) for colID in ([1],[2],[3])) as pv