sql遍历xml,遍历节点并遍历节点值,xm节点属性数据转换成表

--===============================遍历获取xml指定节点和节点包含属性的值===========
declare @GD_list xml;
set @GD_list=(select GD_list from SC_gongdan where GD_bianhao='GD201407190001');	
declare @cur_NodeCount int,@totle_Count int,@Attribs_Count int ,@cur_AttCount int ,@gongxuNode xml;
set @cur_NodeCount=1;
set @totle_Count=(select @GD_list.value('count(//gongxu)','int'));

while(@cur_NodeCount<=@totle_Count)
		begin
				--取得所有gongxu节点
				 set @gongxuNode=(select @Gd_list.query('gongdan/gongxuList/gongxu[position()=sql:variable("@cur_NodeCount")]'));
				--取得每个工序节点的属性数量
				 set @Attribs_Count= (select @gongxuNode.value('count(gongxu/@*)','int'));
				 set @cur_AttCount=1;
				 while(@cur_AttCount<=@Attribs_Count)
				begin
						select @gongxuNode.value('(gongxu/@*[position()=sql:variable("@cur_AttCount")])[1]','nvarchar(100)')
						set @cur_AttCount=@cur_AttCount+1 
				end
				 
				 set @cur_NodeCount=@cur_NodeCount+1;
		end


--=============================将指定节点的属性转换成表数据集===================================================

select 
tbl.col.value('@cp_Name','nvarchar(50)'),
tbl.col.value('@cp_guige','nvarchar(50)'),
tbl.col.value('@cp_NO','nvarchar(50)'),
tbl.col.value('@cp_ShuLiang','int'),
tbl.col.value('@gd_gongqi','nvarchar(50)')
from @GD_list.nodes('//gongxuList/gongxu') tbl(col)