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

I have the following xml and l want to insert it as table to sql server. The problem is that i dont know how to manupilate this structure of xml without unique attributes or elements. I have read a lot of tutorials but as far nothing seems to work.

我有以下xml,我想将其作为表插入到SQL Server。问题是我不知道如何在没有唯一属性或元素的情况下对这个xml结构进行操作。我已经阅读了很多教程,但到目前为止似乎没有任何工作。

<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>

My current source in sql is:

我在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);

Do you have any ideas ?

你有什么想法 ?

1 个解决方案

#1

Well it seems you need to add xml namespace to your xml (<Table xmlns:ss="uri">) and then:

好吧,似乎你需要将xml命名空间添加到你的xml(

),然后:

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)

The result:

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)

#1