久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

SQL-Server XML-Bulk-Import 并讀取為表數據

數據庫問題 html5模板網
SQL-Server XML-Bulk-Import and reading as table-data(SQL-Server XML-Bulk-Import 并讀取為表數據)
本文介紹了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模板網！

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！

相關文檔推薦

Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個子標記轉換為具有多個分隔符的單列-SQL Server (3))
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?(如何將表格的前兩列堆疊成一列，但也僅將第三列與第一列配對?) - IT屋-程序員軟件開發技
Recursive t-sql query(遞歸 t-sql 查詢)
Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱轉換為日期/月份編號(問題和答案的組合))
主站蜘蛛池模板： 国产在线精品一区二区三区 | 国产一区成人 | 无吗视频 | 97久久精品午夜一区二区 | 羞羞的视频免费在线观看 | 亚洲一卡二卡 | 欧美伊人久久久久久久久影院 | 美女日批免费视频 | 久久久一区二区三区四区 | 国产精品久久久久免费 | 亚洲精品在线播放 | 午夜成人免费视频 | www.免费看片.com | 九九热最新视频 | 日韩av美女电影 | 荷兰欧美一级毛片 | 天天干在线播放 | 精品一区二区三区视频在线观看 | 免费毛片网站在线观看 | 国产精品久久久久无码av | 成人一级毛片 | 成人亚洲 | 久久综合久 | 免费毛片网 | 在线日韩视频 | 成人在线a | 久久精品国产免费高清 | 91久久国产综合久久 | 9久9久9久女女女九九九一九 | 亚洲美女一区二区三区 | 国产免费视频 | 中文在线日韩 | 欧洲视频一区二区 | 成人国产精品免费观看 | 久久久精品一区二区三区四季av | av在线免费播放 | 国产精品亚洲成在人线 | 中文字幕一区二区三区日韩精品 | 性色av香蕉一区二区 | 牛牛热在线视频 | 久久久毛片 |