我有以下問題，不知道如何將資料從嵌套的 xml 檔案匯入資料庫。
這里是xml檔案的例子：
<pm001 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="T1_pm001.xsd">
<reportHeader>
<exchName>Xetra</exchName>
<reportCode>PM001</reportCode>
<reportName>Example Report for Stocks</reportName>
<memberId>XXETF</memberId>
<memberName>Xetra ETF</memberName>
<reportDate>2022-01-02</reportDate>
</reportHeader>
<pm001Grp>
<pm001Grp1>
<pm001KeyGrp1>
<unitGrp>
<unitCode>ABC01</unitCode>
<lngName>ABC Company 01</lngName>
<unitId>10000</unitId>
</unitGrp>
</pm001KeyGrp1>
<pm001Grp2>
<pm001KeyGrp2>
<instrumentGrp1>
<product>Stock1</product>
<instType>1</instType>
<instId>250000</instId>
<instShortcode>ELL01</instShortcode>
<instNam>Example Stock 1</instNam>
<currencyCode>EUR</currencyCode>
</instrumentGrp1>
</pm001KeyGrp2>
<pm001Rec>
<tradingDate>2022-01-01</tradingDate>
<numTradesDay>8</numTradesDay>
<rateVola>100</rateVola>
</pm001Rec>
</pm001Grp2>
<pm001Grp2>
<pm001KeyGrp2>
<instrumentGrp1>
<product>Stock2</product>
<instType>1</instType>
<instId>251400</instId>
<instShortcode>ELL02</instShortcode>
<instNam>Example Stock 2</instNam>
<currencyCode>EUR</currencyCode>
</instrumentGrp1>
</pm001KeyGrp2>
<pm001Rec>
<tradingDate>2022-01-01</tradingDate>
<numTradesDay>2</numTradesDay>
<rateVola>90</rateVola>
</pm001Rec>
</pm001Grp2>
<pm001Grp2>
<pm001KeyGrp2>
<instrumentGrp1>
<product>Stock3</product>
<instType>1</instType>
<instId>260000</instId>
<instShortcode>ELL03</instShortcode>
<instNam>Example Stock 3</instNam>
<currencyCode>EUR</currencyCode>
</instrumentGrp1>
</pm001KeyGrp2>
<pm001Rec>
<tradingDate>2022-01-01</tradingDate>
<numTradesDay>6</numTradesDay>
<rateVola>0</rateVola>
</pm001Rec>
</pm001Grp2>
</pm001Grp1>
</pm001Grp>
<pm001Grp>
<pm001Grp1>
<pm001KeyGrp1>
<unitGrp>
<unitCode>DEF02</unitCode>
<lngName>DEF Company 02</lngName>
<unitId>11000</unitId>
</unitGrp>
</pm001KeyGrp1>
<pm001Grp2>
<pm001KeyGrp2>
<instrumentGrp1>
<product>Stock1</product>
<instType>1</instType>
<instId>250000</instId>
<instShortcode>ELL01</instShortcode>
<instNam>Example Stock 1</instNam>
<currencyCode>EUR</currencyCode>
</instrumentGrp1>
</pm001KeyGrp2>
<pm001Rec>
<tradingDate>2022-01-01</tradingDate>
<numTradesDay>3</numTradesDay>
<rateVola>1</rateVola>
</pm001Rec>
</pm001Grp2>
<pm001Grp2>
<pm001KeyGrp2>
<instrumentGrp1>
<product>Stock2</product>
<instType>1</instType>
<instId>251400</instId>
<instShortcode>ELL02</instShortcode>
<instNam>Example Stock 2</instNam>
<currencyCode>EUR</currencyCode>
</instrumentGrp1>
</pm001KeyGrp2>
<pm001Rec>
<tradingDate>2022-01-01</tradingDate>
<numTradesDay>35</numTradesDay>
<rateVola>45</rateVola>
</pm001Rec>
</pm001Grp2>
<pm001Grp2>
<pm001KeyGrp2>
<instrumentGrp1>
<product>Stock3</product>
<instType>1</instType>
<instId>260000</instId>
<instShortcode>ELL03</instShortcode>
<instNam>Example Stock 3</instNam>
<currencyCode>EUR</currencyCode>
</instrumentGrp1>
</pm001KeyGrp2>
<pm001Rec>
<tradingDate>2022-01-01</tradingDate>
<numTradesDay>34</numTradesDay>
<rateVola>60</rateVola>
</pm001Rec>
</pm001Grp2>
</pm001Grp1>
</pm001Grp>
</pm001>
資料需要在我的 sql server 資料庫中的表中：
exchName VARCHAR(10) NOT NULL,
reportCode VARCHAR(5) NOT NULL,
reportName VARCHAR(MAX) NOT NULL,
memberId VARCHAR(5) NOT NULL,
memberName VARCHAR(MAX) NOT NULL,
reportDate DATETIME NOT NULL,
unitCode VARCHAR(5) NOT NULL,
lngName VARCHAR(MAX) NOT NULL,
unitId INT NOT NULL,
product VARCHAR(MAX) NOT NULL,
instType TINYINT NOT NULL,
instId INT NOT NULL,
instShortcode VARCHAR(5) NOT NULL,
instNam VARCHAR(MAX) NOT NULL,
currencyCode CHAR(3) NOT NULL,
tradingDate DATETIME NOT NULL,
numTradesDay INT NOT NULL,
rateVola INT NOT NULL
我必須在這些節點之間建立連接：
XML nodes:
'pm001/reportHeader'
'pm001/pm001Grp/pm001Grp1/pm001KeyGrp1/unitGrp'
'pm001/pm001Grp/pm001Grp1/pm001Grp2/pm001KeyGrp2/instrumentGrp1'
'pm001/pm001Grp/pm001Grp1/pm001Grp2/pm001Rec'
有誰知道如何處理這個問題？
uj5u.com熱心網友回復：
我建議您不要使用舊OPENXML功能。它已被棄用并且有很多問題。
相反，您可以使用較新的 XQuery.nodes和.value函式。
您只需要選擇要粉碎的節點，然后使用CROSS APPLY. 只需INSERT在頂部添加即可實際插入表格
SELECT
x2.reportHeader.value('(exchName/text())[1]','varchar(10)'),
x2.reportHeader.value('(reportCode/text())[1]','varchar(5)'),
x2.reportHeader.value('(reportName/text())[1]','varchar(max)'),
x2.reportHeader.value('(memberId/text())[1]','varchar(5)'),
x2.reportHeader.value('(memberName/text())[1]','varchar(max)'),
x2.reportHeader.value('(reportDate/text())[1]','date'),
x4.unitGrp.value('(unitCode/text())[1]','varchar(5)'),
x4.unitGrp.value('(lngName/text())[1]','varchar(max)'),
x4.unitGrp.value('(unitId/text())[1]','int'),
x5.pmGrp2.value('(pm001KeyGrp2/instrumentGrp1/product/text())[1]','varchar(max)'),
x5.pmGrp2.value('(pm001KeyGrp2/instrumentGrp1/instType/text())[1]','tinyint'),
x5.pmGrp2.value('(pm001KeyGrp2/instrumentGrp1/instId/text())[1]','int'),
x5.pmGrp2.value('(pm001KeyGrp2/instrumentGrp1/instShortcode/text())[1]','varchar(5)'),
x5.pmGrp2.value('(pm001KeyGrp2/instrumentGrp1/instNam/text())[1]','varchar(max)'),
x5.pmGrp2.value('(pm001KeyGrp2/instrumentGrp1/currencyCode/text())[1]','char(3)'),
x5.pmGrp2.value('(pm001Rec/tradingDate/text())[1]','date'),
x5.pmGrp2.value('(pm001Rec/numTradesDay/text())[1]','int'),
x5.pmGrp2.value('(pm001Rec/rateVola/text())[1]','int')
FROM @xml.nodes('pm001') x1(pm001)
CROSS APPLY x1.pm001.nodes('reportHeader') x2(reportHeader)
CROSS APPLY x1.pm001.nodes('pm001Grp/pm001Grp1') x3(pmGrp)
CROSS APPLY x3.pmGrp.nodes('pm001KeyGrp1/unitGrp') x4(unitGrp)
CROSS APPLY x3.pmGrp.nodes('pm001Grp2') x5(pmGrp2);
db<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/425860.html
標籤：sql sql服务器 xml tsql xquery-sql