SQL-Server XML-Bulk-Import 并读取为表数据

时间：2023-02-21

本文介绍了SQL-Server XML-Bulk-Import 并读取为表数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有以下问题:

对于 XML-Import 到 SQL-Sever，我使用以下代码:

For the XML-Import into SQL-Sever, I use this code:

DROP TABLE XMLwithOpenXML

CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK '\\WINSER1\\proALPHA\\templates_eBus\\Test.xml', SINGLE_BLOB) AS x;

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

它工作正常.但是这里在 XML 中，我不知道，我该怎么办:

It works fine. But here in the XML, I don't know, what should I do:

    <MIME_INFO>
        <MIME>
            <MIME_TYPE>image/jpeg</MIME_TYPE>
            <MIME_SOURCE>ube105252.jpg</MIME_SOURCE>
            <MIME_PURPOSE>normal</MIME_PURPOSE>
            <MIME_ORDER>1</MIME_ORDER>
        </MIME>
        <MIME>
            <MIME_TYPE>image/jpeg</MIME_TYPE>
            <MIME_SOURCE>bbd372670.jpg</MIME_SOURCE>
            <MIME_PURPOSE>logo</MIME_PURPOSE>
            <MIME_ORDER>2</MIME_ORDER>
        </MIME>
    </MIME_INFO>

我的用户，同时需要-Blocks.但它们的名字相同！

An user of me, needs both <MIME>-Blocks. But they are named the same!

我怎样才能在 2 个 <Mime>-Tags 中得到这 8 行?重命名不是解决方案，因为 XML 有超过 2.000.000 行！

How can I get this 8 rows contented in the 2 <Mime>-Tags? Rename is not the solution, because the XML has over 2.000.000 rows!

谢谢.

编辑 16:20这是上面的其余代码.使用此标签可以正常工作:

EDIT 16:20 Here the rest of the code above. With this tags it works fine:

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT  [SUPPLIER_AID]
       ,REFERENCE_FEATURE_SYSTEM_NAME
       ,REFERENCE_FEATURE_GROUP_ID


FROM OPENXML(@hDoc, 'BMECAT/T_NEW_CATALOG/ARTICLE')
--FROM OPENXML(@hDoc, 'BMECAT/T_NEW_CATALOG/ARTICLE/ARTICLE_ORDER_DETAILS')
--## Hier werden die gewünschten Columns deklariert.
WITH 
(
     SUPPLIER_AID [varchar](25) 'SUPPLIER_AID'
    ,REFERENCE_FEATURE_SYSTEM_NAME [varchar](25) 'REFERENCE_FEATURE_SYSTEM_NAME'
    ,REFERENCE_FEATURE_GROUP_ID [varchar](25) 'REFERENCE_FEATURE_GROUP_ID'

)

-----------------------------编辑 16092016/08:14-----------------------------

-----------------------------EDIT 16092016 / 08:14-----------------------------

我还是不明白你的代码，因为你使用的不是真正的表XMLwithOpenXML".Hier 是一篇大约 20.000 的 XML 文章:

I still don't understand your code, because you use not the real table "XMLwithOpenXML". Hier is one article of ~ 20.000 in the XML:

<BMECAT>
    <T_NEW_CATALOG>
        <ARTICLE mode="new">
            <SUPPLIER_AID>9900026005</SUPPLIER_AID>
            <MIME_INFO>
                <MIME>
                    <MIME_TYPE>image/jpeg</MIME_TYPE>
                    <MIME_SOURCE>ube105252.jpg</MIME_SOURCE>
                    <MIME_PURPOSE>normal</MIME_PURPOSE>
                    <MIME_ORDER>1</MIME_ORDER>
                </MIME>
                <MIME>
                    <MIME_TYPE>image/jpeg</MIME_TYPE>
                    <MIME_SOURCE>bbd372670.jpg</MIME_SOURCE>
                    <MIME_PURPOSE>logo</MIME_PURPOSE>
                    <MIME_ORDER>2</MIME_ORDER>
                </MIME>
                <MIME>
                    <MIME_TYPE>image/jpeg</MIME_TYPE>
                    <MIME_SOURCE>ube305149.jpg</MIME_SOURCE>
                    <MIME_PURPOSE>logo</MIME_PURPOSE>
                    <MIME_ORDER>3</MIME_ORDER>
                </MIME>
                <MIME>
                    <MIME_TYPE>image/jpeg</MIME_TYPE>
                    <MIME_SOURCE>ube108453.jpg</MIME_SOURCE>
                    <MIME_PURPOSE>others</MIME_PURPOSE>
                    <MIME_ORDER>4</MIME_ORDER>
                </MIME>
                <MIME>
                    <MIME_TYPE>application/pdf</MIME_TYPE>
                    <MIME_SOURCE>ube007100.pdf</MIME_SOURCE>
                    <MIME_PURPOSE>others</MIME_PURPOSE>
                    <MIME_ORDER>5</MIME_ORDER>
                </MIME>
            </MIME_INFO>
        </ARTICLE>
    </T_NEW_CATALOG>
</BMECAT>

您会看到有一个 SUPPLIER_AID 和四次标签.我只需要第一个和第二个(正常和徽标).在这种情况下，SUPPLIER_AID 是什么?我认为代码必须看起来像:

You see there is one SUPPLIER_AID and four times a <MIME>-tag. I need only the first and the second (where normal and logo). What is in this case with the SUPPLIER_AID? I think the code has to look like:

WITH Numbered AS
(
    SELECT LoadedDateTime
          ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ID
          --,a.query('.') AS SUPPLIER_AID
          ,m.query('.') AS mime
    FROM XMLwithOpenXML AS t
    CROSS APPLY t.XMLData.nodes('BMECAT/T_NEW_CATALOG/ARTICLE/MIME_INFO/MIME') AS A(m)
)
SELECT ID
  --,[SUPPLIER_AID].value('(ARTICLE)[1]','nvarchar(max)') AS SUPPLIER_AID
    ,mime.value('(MIME/MIME_TYPE)[1]','nvarchar(max)') AS MIME_TYPE
    ,mime.value('(MIME/MIME_SOURCE)[1]','nvarchar(max)') AS MIME_SOURCE
    ,mime.value('(MIME/MIME_PURPOSE)[1]','nvarchar(max)') AS MIME_PURPOSE
    ,mime.value('(MIME/MIME_ORDER)[1]','nvarchar(max)') AS MIME_ORDER
FROM Numbered

使用新代码，我明白了:

With the new code, I get this:

+-----------+--------------+-------------+-----------+
|MIME_TYPE  |MIME_SOURCE   |MIME_PURPOSE |MIME_ORDER |
+-----------+--------------+-------------+-----------+
|image/jpeg |ube105252.jpg |normal       |1          |
+-----------+--------------+-------------+-----------+
|image/jpeg |bbd372670.jpg |logo         |2          |
+-----------+--------------+-------------+-----------+
|image/jpeg |ube105252.jpg |logo         |3          |
+-----------+--------------+-------------+-----------+
|image/jpeg |bbd372670.jpg |others       |4          |
+-----------+--------------+-------------+-----------+
|image/jpeg |bbd372670.jpg |others       |5          |
+-----------+--------------+-------------+-----------+

但我需要的是:

+-------------+------------+------------------+--------------+-------------+
|SUPPLIER_AID | MIME_TYPE  |    MIME_SOURCE   | MIME_PURPOSE |  MIME_ORDER |
+-------------+------------+------------------+--------------+-------------+
|9900026005   | image/jpeg |    ube105252.jpg | normal       |  1          |
+-------------+------------+------------------+--------------+-------------+
|9900026005   | image/jpeg |    bbd372670.jpg | logo         |  2          |
+-------------+------------+------------------+--------------+-------------+

推荐答案

您使用 FROM OPENXML 的方法已经过时，不应再使用.有更好的 XML 方法，例如 .node()、.value()、.query() 和 .modify().


Your approach with FROM OPENXML is outdated and should not be used any more. There are much better XML methods like .node(), .value(), .query() and .modify().
将 XML 放入表中的方式非常好.一旦你有了它，你应该像这样继续:
The way you get the XML into your table is quite OK. Once you have it there, you should continue like this:
注意我使用声明的模型表来模拟您的表.
Attention I use a declared mock-up-table to simulate your table.
DECLARE @XMLwithOpenXML TABLE(XMLData XML,LoadedDateTime DATETIME);

INSERT INTO @XMLwithOpenXML VALUES
('<MIME_INFO>
    <MIME>
        <MIME_TYPE>image/jpeg</MIME_TYPE>
        <MIME_SOURCE>ube105252.jpg</MIME_SOURCE>
        <MIME_PURPOSE>normal</MIME_PURPOSE>
        <MIME_ORDER>1</MIME_ORDER>
    </MIME>
    <MIME>
        <MIME_TYPE>image/jpeg</MIME_TYPE>
        <MIME_SOURCE>bbd372670.jpg</MIME_SOURCE>
        <MIME_PURPOSE>logo</MIME_PURPOSE>
        <MIME_ORDER>2</MIME_ORDER>
    </MIME>
</MIME_INFO>',GETDATE());

此时，您的 XML 已成功纳入您的表中
At this point, your XML is successfully taken into your table
CTE编号"将使用 .nodes() 以固有顺序读取所有 MIME 元素并相应地编号.
The CTE "Numbered" will read all MIME elements using .nodes() in there inherent order and number them accordingly. 
SELECT 拉取实际数据
WITH Numbered AS
(
    SELECT LoadedDateTime
          ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ID
          ,m.query('.') AS mime
    FROM @XMLwithOpenXML AS t
    CROSS APPLY t.XMLData.nodes('/MIME_INFO/MIME') AS A(m)
)
SELECT ID
      ,LoadedDateTime
      ,mime.value('(MIME/MIME_TYPE)[1]','nvarchar(max)') AS MIME_TYPE
      ,mime.value('(MIME/MIME_SOURCE)[1]','nvarchar(max)') AS MIME_SOURCE
      ,mime.value('(MIME/MIME_PURPOSE)[1]','nvarchar(max)') AS MIME_PURPOSE
      ,mime.value('(MIME/MIME_ORDER)[1]','nvarchar(max)') AS MIME_ORDER
FROM Numbered

结果
+----+-------------------------+------------+---------------+--------------+------------+
| ID | LoadedDateTime          | MIME_TYPE  | MIME_SOURCE   | MIME_PURPOSE | MIME_ORDER |
+----+-------------------------+------------+---------------+--------------+------------+
| 1  | 2016-09-15 16:37:30.730 | image/jpeg | ube105252.jpg | normal       | 1          |
+----+-------------------------+------------+---------------+--------------+------------+
| 2  | 2016-09-15 16:37:30.730 | image/jpeg | bbd372670.jpg | logo         | 2          |
+----+-------------------------+------------+---------------+--------------+------------+

更新
您没有显示完整的 XML...在上面给出的示例中，此代码提取了您可能想知道的所有内容:


UPDATE

You did not show the full XML... With the example given above this code extracts all you might want to knwo:
WITH Numbered AS
(
    SELECT Id
          ,LoadedDateTime
          ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS MimeRowNr
          ,a.value('@mode','nvarchar(max)') ARTICLE_MODE
          ,a.value('SUPPLIER_AID[1]','nvarchar(max)') AS SUPPLIER_AID
          ,m.query('.') AS mime
    FROM XMLwithOpenXML AS t
    CROSS APPLY t.XMLData.nodes('/BMECAT/T_NEW_CATALOG/ARTICLE') AS A(a)
    CROSS APPLY a.nodes('MIME_INFO/MIME') AS B(m)
)
SELECT Id
      ,MimeRowNr
      ,LoadedDateTime
      ,ARTICLE_MODE
      ,SUPPLIER_AID
      ,mime.value('(MIME/MIME_TYPE)[1]','nvarchar(max)') AS MIME_TYPE
      ,mime.value('(MIME/MIME_SOURCE)[1]','nvarchar(max)') AS MIME_SOURCE
      ,mime.value('(MIME/MIME_PURPOSE)[1]','nvarchar(max)') AS MIME_PURPOSE
      ,mime.value('(MIME/MIME_ORDER)[1]','int') AS MIME_ORDER
FROM Numbered;

结果
+----+-----------+-------------------------+--------------+--------------+-----------------+---------------+--------------+------------+
| Id | MimeRowNr | LoadedDateTime          | ARTICLE_MODE | SUPPLIER_AID | MIME_TYPE       | MIME_SOURCE   | MIME_PURPOSE | MIME_ORDER |
+----+-----------+-------------------------+--------------+--------------+-----------------+---------------+--------------+------------+
| 1  | 1         | 2016-09-16 09:32:53.570 | new          | 9900026005   | image/jpeg      | ube105252.jpg | normal       | 1          |
+----+-----------+-------------------------+--------------+--------------+-----------------+---------------+--------------+------------+
| 1  | 2         | 2016-09-16 09:32:53.570 | new          | 9900026005   | image/jpeg      | bbd372670.jpg | logo         | 2          |
+----+-----------+-------------------------+--------------+--------------+-----------------+---------------+--------------+------------+
| 1  | 3         | 2016-09-16 09:32:53.570 | new          | 9900026005   | image/jpeg      | ube305149.jpg | logo         | 3          |
+----+-----------+-------------------------+--------------+--------------+-----------------+---------------+--------------+------------+
| 1  | 4         | 2016-09-16 09:32:53.570 | new          | 9900026005   | image/jpeg      | ube108453.jpg | others       | 4          |
+----+-----------+-------------------------+--------------+--------------+-----------------+---------------+--------------+------------+
| 1  | 5         | 2016-09-16 09:32:53.570 | new          | 9900026005   | application/pdf | ube007100.pdf | others       | 5          |
+----+-----------+-------------------------+--------------+--------------+-----------------+---------------+--------------+------------+


                        这篇关于SQL-Server XML-Bulk-Import 并读取为表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！



上一篇：SQL 查询运行速度比存储过程快 
下一篇：将 SQL 表名附加到今天的日期 

 
相关文章
将每个子标记转换为具有多个分隔符的单列-SQLConverting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(将每个子标记转换为具有多个分隔符的单列-SQ
如何从多个表创建视图?How can I create a view from more than one table?(如何从多个表创建视图?)
根据前一行内的计算值创建计算值Create calculated value based on calculated value inside previous row(根据前一行内的计算值创建计算值)
如何将表格的前两列堆叠成一列，但也仅将第三How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何将表格的前两列堆
递归 t-sql 查询Recursive t-sql query(递归 t-sql 查询)
将月份名称转换为日期/月份编号(问题和答案的组Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(将月份名称转换为日期/月份编号(问题和答案的组合



最新文章

在 SQL Server 中显示 COUNT = 0 的行
T-SQL - 获取最近的日期和最近的未来日期
SQL Server 使用通配符加入并在第一场比赛时停止
为什么 SQL Server 找不到新创建的函数?
检查具有多个输入参数的约束 UDF 不起作用
如何仅与月份和年份进行比较，而不是完整日期
我可以在 Sql Server 中对一组数字执行按位和吗?
使用 2 个以上表的联合覆盖按字母顺序排列的默
将xml数据插入表存储过程sql server
使用 T-SQL 创建表 - 在对象资源管理器中看不到创