将xml插入到没有唯一属性的sql server中

问题描述：

我有以下xml，我想将它作为表插入到sql server中。问题是我不知道如何在没有独特属性或元素的情况下手工创建xml结构。我已经阅读了很多教程，但似乎没有任何工作。将xml插入到没有唯一属性的sql server中

<Table> 
    <Row> 
    <Cell><Data ss:Type="Number">157882</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">441944.8990</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">211469.0100</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">1838.8610</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">1.9955</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">2.2767</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">0.2252</Data></Cell> 
    <Cell><Data ss:Type="Number">0</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">63.5774</Data></Cell> 
    <Cell><Data ss:Type="Number">115</Data></Cell> 
    <Cell><Data ss:Type="Number">15</Data></Cell> 
    <Cell><Data ss:Type="Number">50</Data></Cell> 
    <Cell><Data ss:Type="Number">72</Data></Cell> 
    </Row> 
    <Row> 
    <Cell><Data ss:Type="Number">76922</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">441944.8990</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">211469.0100</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">1838.8610</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">1.9955</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">2.2767</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">0.2252</Data></Cell> 
    <Cell><Data ss:Type="Number">0</Data></Cell> 
    <Cell ss:StyleID="ce1"><Data ss:Type="Number">63.5774</Data></Cell> 
    <Cell><Data ss:Type="Number">115</Data></Cell> 
    <Cell><Data ss:Type="Number">15</Data></Cell> 
    <Cell><Data ss:Type="Number">72</Data></Cell> 
    <Cell><Data ss:Type="Number">50</Data></Cell> 
    </Row> </Table>

我在SQL电流源：

INSERT INTO report (var1,var2,var3....var13) 
SELECT X.Cell.query('Data').value('.', 'VARCHAR(30)'), 
     X.Cell.query('Data').value('.', 'VARCHAR(30)'), 
     X.Cell.query('Data').value('.', 'VARCHAR(30)') 
           . 
           . 
           .          . 
           . 
FROM ( 
SELECT CAST(x AS XML) 
FROM OPENROWSET(
    BULK 'C:\xml_import.xml', 
    SINGLE_BLOB) AS T(x) 
    ) AS T(x) 
CROSS APPLY x.nodes('Table/Row') AS X(Cell);

你有什么想法？

答

嗯，看来你需要添加xml namespace您xml（<Table xmlns:ss="uri">），然后：

DECLARE @xml xml = 
'<Table xmlns:ss="uri"> 
    <Row> 
     <Cell><Data ss:Type="Number">157882</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">441944.8990</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">211469.0100</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">1838.8610</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">1.9955</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">2.2767</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">0.2252</Data></Cell> 
     <Cell><Data ss:Type="Number">0</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">63.5774</Data></Cell> 
     <Cell><Data ss:Type="Number">115</Data></Cell> 
     <Cell><Data ss:Type="Number">15</Data></Cell> 
     <Cell><Data ss:Type="Number">50</Data></Cell> 
     <Cell><Data ss:Type="Number">72</Data></Cell> 
    </Row> 
    <Row> 
     <Cell><Data ss:Type="Number">76922</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">441944.8990</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">211469.0100</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">1838.8610</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">1.9955</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">2.2767</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">0.2252</Data></Cell> 
     <Cell><Data ss:Type="Number">0</Data></Cell> 
     <Cell ss:StyleID="ce1"><Data ss:Type="Number">63.5774</Data></Cell> 
     <Cell><Data ss:Type="Number">115</Data></Cell> 
     <Cell><Data ss:Type="Number">15</Data></Cell> 
     <Cell><Data ss:Type="Number">72</Data></Cell> 
     <Cell><Data ss:Type="Number">50</Data></Cell> 
    </Row> 
</Table>'; 


SELECT n.r.value('(Cell/Data)[1]','nvarchar(20)') as var1, 
     n.r.value('(Cell/Data)[2]','nvarchar(20)') as var2, 
     n.r.value('(Cell/Data)[3]','nvarchar(20)') as var3, 
     n.r.value('(Cell/Data)[4]','nvarchar(20)') as var4, 
     n.r.value('(Cell/Data)[5]','nvarchar(20)') as var5, 
     n.r.value('(Cell/Data)[6]','nvarchar(20)') as var6, 
     n.r.value('(Cell/Data)[7]','nvarchar(20)') as var7, 
     n.r.value('(Cell/Data)[8]','nvarchar(20)') as var8, 
     n.r.value('(Cell/Data)[9]','nvarchar(20)') as var9, 
     n.r.value('(Cell/Data)[10]','nvarchar(20)') as var10, 
     n.r.value('(Cell/Data)[11]','nvarchar(20)') as var11, 
     n.r.value('(Cell/Data)[12]','nvarchar(20)') as var12, 
     n.r.value('(Cell/Data)[13]','nvarchar(20)') as var13 
FROM @xml.nodes('Table/Row') as n(r)

结果：

var1     var2     var3     var4     var5     var6     var7     var8     var9     var10    var11    var12    var13 
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 
157882    441944.8990   211469.0100   1838.8610   1.9955    2.2767    0.2252    0     63.5774    115     15     50     72 
76922    441944.8990   211469.0100   1838.8610   1.9955    2.2767    0.2252    0     63.5774    115     15     72     50 

(2 row(s) affected)

将xml插入到没有唯一属性的sql server中

相关推荐