xml 格式如下:
<folders name= 成绩单>
-- <folder title="A" id='1' >
------ <folder title="B" id='2' />
------ <folder title="C" id ='3'/>
------ <folder title="D" id ='4'/>
-- </folder>
-- <folder title="E" id ='5'/>
-- <folder title="F" id ='6' />
-- <folder title="G" id ='7'/>
-- <folder title="H" id ='8'/>
</folders>数据库表结构如下:
id
parentid
title我想把xml按照他的父子关系的层级,写入对应的sql中去,生成如下的形式
id parentid title
1 -1 A
2 1 B
3 1 C
4 1 D
5 -1 E
6 5 F
7 -1 G
8 -1 H
用openxml改怎么写呢?谢谢各位指导了
<folders name= 成绩单>
-- <folder title="A" id='1' >
------ <folder title="B" id='2' />
------ <folder title="C" id ='3'/>
------ <folder title="D" id ='4'/>
-- </folder>
-- <folder title="E" id ='5'/>
-- <folder title="F" id ='6' />
-- <folder title="G" id ='7'/>
-- <folder title="H" id ='8'/>
</folders>数据库表结构如下:
id
parentid
title我想把xml按照他的父子关系的层级,写入对应的sql中去,生成如下的形式
id parentid title
1 -1 A
2 1 B
3 1 C
4 1 D
5 -1 E
6 5 F
7 -1 G
8 -1 H
用openxml改怎么写呢?谢谢各位指导了
SET @x = '<folders name= "成绩单">
<folder title="A" id="1" >
<folder title="B" id="2" />
<folder title="C" id ="3"/>
<folder title="D" id ="4"/>
</folder>
<folder title="E" id ="5"/>
<folder title="F" id ="6" />
<folder title="G" id ="7"/>
<folder title="H" id ="8"/>
</folders>
'SELECT
T.x.value('@id','int') AS id,
ISNULL(T.x.value('../@id','int'),-1) AS parentid,
T.x.value('@title','varchar(50)') AS title
FROM @x.nodes('//folder') AS T(x)
select ...