sql XML转table

将查询出来的datatable 转XML  在传到sql里面转成sql里面的table

   declare @Xml xml
        Declare @sql Nvarchar(max)
        set @Xml='<root><columnList><columnItem columnName="sStyleCode"></columnItem><columnItem columnName="sColorName"></columnItem><columnItem columnName="sSizeName"></columnItem><columnItem columnName="sCcount"></columnItem><columnItem columnName="sAddCount"></columnItem><columnItem columnName="sIsHeadLevel"></columnItem></columnList><dataTable><dataRow><sStyleCode>L10351</sStyleCode><sColorName>绿色</sColorName><sSizeName>Head</sSizeName><sCcount>25860</sCcount><sAddCount>1140</sAddCount><sIsHeadLevel>1</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>绿色</sColorName><sSizeName>34</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>绿色</sColorName><sSizeName>35</sSizeName><sCcount>1687</sCcount><sAddCount>313</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>绿色</sColorName><sSizeName>36</sSizeName><sCcount>2829</sCcount><sAddCount>171</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>绿色</sColorName><sSizeName>37</sSizeName><sCcount>3828</sCcount><sAddCount>172</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>绿色</sColorName><sSizeName>38</sSizeName><sCcount>4828</sCcount><sAddCount>172</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>绿色</sColorName><sSizeName>39</sSizeName><sCcount>5838</sCcount><sAddCount>162</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>绿色</sColorName><sSizeName>40</sSizeName><sCcount>6850</sCcount><sAddCount>150</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>绿色</sColorName><sSizeName>41</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>绿色</sColorName><sSizeName>42</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>黄色</sColorName><sSizeName>Head</sSizeName><sCcount>26940</sCcount><sAddCount>60</sAddCount><sIsHeadLevel>1</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>黄色</sColorName><sSizeName>34</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>黄色</sColorName><sSizeName>35</sSizeName><sCcount>1992</sCcount><sAddCount>8</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>黄色</sColorName><sSizeName>36</sSizeName><sCcount>2985</sCcount><sAddCount>15</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>黄色</sColorName><sSizeName>37</sSizeName><sCcount>3985</sCcount><sAddCount>15</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>黄色</sColorName><sSizeName>38</sSizeName><sCcount>4985</sCcount><sAddCount>15</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>黄色</sColorName><sSizeName>39</sSizeName><sCcount>5993</sCcount><sAddCount>7</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>黄色</sColorName><sSizeName>40</sSizeName><sCcount>7000</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>黄色</sColorName><sSizeName>41</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>黄色</sColorName><sSizeName>42</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>粉色</sColorName><sSizeName>Head</sSizeName><sCcount>26910</sCcount><sAddCount>90</sAddCount><sIsHeadLevel>1</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>粉色</sColorName><sSizeName>34</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>粉色</sColorName><sSizeName>35</sSizeName><sCcount>1987</sCcount><sAddCount>13</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>粉色</sColorName><sSizeName>36</sSizeName><sCcount>2979</sCcount><sAddCount>21</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>粉色</sColorName><sSizeName>37</sSizeName><sCcount>3978</sCcount><sAddCount>22</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>粉色</sColorName><sSizeName>38</sSizeName><sCcount>4978</sCcount><sAddCount>22</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>粉色</sColorName><sSizeName>39</sSizeName><sCcount>5988</sCcount><sAddCount>12</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>粉色</sColorName><sSizeName>40</sSizeName><sCcount>7000</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>粉色</sColorName><sSizeName>41</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>粉色</sColorName><sSizeName>42</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>兰色</sColorName><sSizeName>Head</sSizeName><sCcount>26910</sCcount><sAddCount>1090</sAddCount><sIsHeadLevel>1</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>兰色</sColorName><sSizeName>34</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>兰色</sColorName><sSizeName>35</sSizeName><sCcount>1987</sCcount><sAddCount>1013</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>兰色</sColorName><sSizeName>36</sSizeName><sCcount>2979</sCcount><sAddCount>21</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>兰色</sColorName><sSizeName>37</sSizeName><sCcount>3978</sCcount><sAddCount>22</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>兰色</sColorName><sSizeName>38</sSizeName><sCcount>4978</sCcount><sAddCount>22</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>兰色</sColorName><sSizeName>39</sSizeName><sCcount>5988</sCcount><sAddCount>12</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>兰色</sColorName><sSizeName>40</sSizeName><sCcount>7000</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>兰色</sColorName><sSizeName>41</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>兰色</sColorName><sSizeName>42</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>青色</sColorName><sSizeName>Head</sSizeName><sCcount>37801</sCcount><sAddCount>1199</sAddCount><sIsHeadLevel>1</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>青色</sColorName><sSizeName>34</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>青色</sColorName><sSizeName>35</sSizeName><sCcount>3703</sCcount><sAddCount>297</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>青色</sColorName><sSizeName>36</sSizeName><sCcount>4815</sCcount><sAddCount>185</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>青色</sColorName><sSizeName>37</sSizeName><sCcount>5805</sCcount><sAddCount>195</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>青色</sColorName><sSizeName>38</sSizeName><sCcount>6815</sCcount><sAddCount>185</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>青色</sColorName><sSizeName>39</sSizeName><sCcount>7823</sCcount><sAddCount>177</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>青色</sColorName><sSizeName>40</sSizeName><sCcount>8840</sCcount><sAddCount>160</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>青色</sColorName><sSizeName>41</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>青色</sColorName><sSizeName>42</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>玫红</sColorName><sSizeName>Head</sSizeName><sCcount>26560</sCcount><sAddCount>440</sAddCount><sIsHeadLevel>1</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>玫红</sColorName><sSizeName>34</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>玫红</sColorName><sSizeName>35</sSizeName><sCcount>1887</sCcount><sAddCount>113</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>玫红</sColorName><sSizeName>36</sSizeName><sCcount>2929</sCcount><sAddCount>71</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>玫红</sColorName><sSizeName>37</sSizeName><sCcount>3928</sCcount><sAddCount>72</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>玫红</sColorName><sSizeName>38</sSizeName><sCcount>4928</sCcount><sAddCount>72</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>玫红</sColorName><sSizeName>39</sSizeName><sCcount>5938</sCcount><sAddCount>62</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>玫红</sColorName><sSizeName>40</sSizeName><sCcount>6950</sCcount><sAddCount>50</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>玫红</sColorName><sSizeName>41</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow><dataRow><sStyleCode>L10351</sStyleCode><sColorName>玫红</sColorName><sSizeName>42</sSizeName><sCcount>0</sCcount><sAddCount>0</sAddCount><sIsHeadLevel>2</sIsHeadLevel></dataRow></dataTable></root>'
       
 DROP TABLE #tabColumn
-- 存储列名的表
Create table #tabColumn(columnName varchar(50))               
              
              
 DROP TABLE #productNum
-- 存储传入的导出数量表             
Create table #productNum(ID int identity(1,1))
              
DECLARE @hdoc int,
@columnName varchar(200),                -- 列名字符串
@columnNameType varchar(800)             -- 列名带数据类型字符串
              
EXEC sp_xml_preparedocument @hDoc OUTPUT,@Xml          
Insert into #tabColumn(columnName)       
SELECT columnName
FROM OPENXML(@hDoc,'/root/columnList/columnItem',1)  
WITH(
columnName varchar(50)
)      
                           
Select @columnName = Isnull(@columnName + ',','')  + columnName, @columnNameType = Isnull(@columnNameType + ',','') + columnName + ' varchar(50) '
From #tabColumn
SELECT @hDoc
SELECT @columnName
SELECT @columnNameType
              
Set @sql = 'Alter table #productNum add ' + @columnNameType
Exec sp_executesql @sql    
--print       @sql
              
Set @sql = 'Insert into #productNum(' + @columnName + ')  SELECT ' + @columnName + ' FROM OPENXML(@hDoc,''/root/dataTable/dataRow'',2) WITH( ' + @columnNameType + ')'
--print   @sql
Exec sp_executesql @sql,N'@hdoc int',@hdoc             
                           
EXEC sp_xml_removedocument @hDoc
              
select * from #tabColumn
select * from #productNum