国产精品一区二区三区久久久,中文字幕av一区二区,视频二区在线

本文介紹了SQL-Server XML-Bulk-Import 并讀取為表數(shù)據(jù)的處理方法，對(duì)大家解決問題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！

問題描述

我有以下問題:

對(duì)于 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>

我的用戶，同時(shí)需要-Blocks.但它們的名字相同！

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

我怎樣才能在 2 個(gè) <Mime>-Tags 中得到這 8 行?重命名不是解決方案，因?yàn)?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這是上面的其余代碼.使用此標(biāo)簽可以正常工作:

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

我還是不明白你的代碼，因?yàn)槟闶褂玫牟皇钦嬲谋鞽MLwithOpenXML".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>

您會(huì)看到有一個(gè) SUPPLIER_AID 和四次標(biāo)簽.我只需要第一個(gè)和第二個(gè)(正常和徽標(biāo)).在這種情況下，SUPPLIER_AID 是什么?我認(rèn)為代碼必須看起來像:

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 的方法已經(jīng)過時(shí)，不應(yīng)再使用.有更好的 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 放入表中的方式非常好.一旦你有了它，你應(yīng)該像這樣繼續(xù):
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());

此時(shí)，您的 XML 已成功納入您的表中
At this point, your XML is successfully taken into your table
CTE編號(hào)"將使用 .nodes() 以固有順序讀取所有 MIME 元素并相應(yīng)地編號(hào).
The CTE "Numbered" will read all MIME elements using .nodes() in there inherent order and number them accordingly. 
SELECT 拉取實(shí)際數(shù)據(jù)
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

結(jié)果
+----+-------------------------+------------+---------------+--------------+------------+
| 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...在上面給出的示例中，此代碼提取了您可能想知道的所有內(nèi)容:


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;

結(jié)果
+----+-----------+-------------------------+--------------+--------------+-----------------+---------------+--------------+------------+
| 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          |
+----+-----------+-------------------------+--------------+--------------+-----------------+---------------+--------------+------------+


                        這篇關(guān)于SQL-Server XML-Bulk-Import 并讀取為表數(shù)據(jù)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！
                【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請(qǐng)聯(lián)系我們刪除處理，感謝您的支持！

pbootcms网站模板|日韩1区2区|织梦模板||网站源码|日韩1区2区|jquery建站特效-html5模板网

SQL-Server XML-Bulk-Import 并讀取為表數(shù)據(jù)

問題描述

推薦答案

更新

UPDATE

相關(guān)文檔推薦