解析xml文件的问题
我有个xml文件，大概如下：
<ROOT>
<TABLE>
<tr>
<td>1111</td>
<td>2222</td>
<td>ASDF</td>
</tr>
<tr>
<td>5678</td>
<td>KJLI</td>
<td>6HJ</td>
</tr>
</TABLE>
</ROOT>
想解析为一个表单的数据
1111,2222,ASDF
5678,KJLI,6HJ
请问sp_xml_preparedocument之后可以做到么？
如果信息不够，添加类似<TABLE row="2" col="3">的信息来标示行*列的数量，这样可以么？
谢谢大家
[解决办法]
DECLARE @idoc INTDECLARE @doc NVARCHAR(4000); SET @doc= '<?xml version="1.0" encoding="UTF-16" ?><ROOT><TABLE><tr> <td>1111</td> <td>2222</td> <td>ASDF</td></tr><tr> <td>5678</td> <td>KJLI</td> <td>6HJ</td></tr></TABLE></ROOT>' EXEC sp_xml_preparedocument @idoc OUTPUT, @doc ;WITH base AS ( SELECT * FROM OPENXML (@idoc,'/ROOT/TABLE',2) )SELECT t.id AS rowid,t.localname,c.text,p.id AS colidINTO #tmpFROM base p INNER JOIN base cON p.id=c.parentidINNER JOIN ( SELECT DISTINCT id,localname FROM base WHERE localname='tr')tON p.parentid=t.idWHERE c.text IS NOT NULL;WITH base AS ( SELECT * ,ROW_NUMBER() OVER(PARTITION BY rowid ORDER BY colid) AS rn FROM #tmp)SELECT distinct t1.rowid,convert(varchar,t1.text) AS col1,convert(varchar,t2.text) AS col2,convert(varchar,t3.text) AS col3FROM base t1 INNER JOIN base t2 ON t1.rowid=t2.rowidINNER JOIN base t3ON t1.rowid=t3.rowidWHERE t1.rn=1AND t2.rn=2AND t3.rn=3EXEC sp_xml_removedocument @idoc DROP TABLE #tmp;
