serg-52 (10/30/2014)

---

🙂

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