问将不带唯一属性的xml插入sql server
EN

Stack Overflow用户

提问于 2015-10-14 18:11:12

回答 1查看 39关注 0票数 1

我有下面的xml，l想把它作为表格插入到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>

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

你有什么想法吗？

sql-server

xml

回答 1

Stack Overflow用户

发布于 2016-03-01 21:46:59

似乎您需要将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)

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/33122551

复制

相似问题

问将不带唯一属性的xml插入sql server
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将不带唯一属性的xml插入sql serverEN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将不带唯一属性的xml插入sql server
EN