英文:
Import large XML file into SQL Server
问题
我有另一个包含132812行的XML文件，加载2874条记录到SQL后，导入速度非常慢。如果只有10条记录，速度非常快。
我也在第2行遇到了问题：
<Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="urn:iso:std:iso:20022:tech:xsd:pain.002.001.03">
无法正常工作，但是
<Document>
可以正常工作。
这是我的XML文件：
<?xml version="1.0" encoding="UTF-8"?>
<Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:iso:std:iso:20022:tech:xsd:pain.002.001.03">
<CstmrPmtStsRpt>
...
（以下内容太多，请自行翻译）
</CstmrPmtStsRpt>
</Document>
请注意，我只翻译了一部分内容，剩下的内容太多了，您可以自行翻译。
英文:
I have another XML file with 132812 lines and after loading 2874 records into SQL, but is very slower to import. If I have only 10 records, it is very fast.
I have also a problem on line 2:
<Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="urn:iso:std:iso:20022:tech:xsd:pain.002.001.03">
doesn't work but
<Document>
works.
This is my XML file:
<?xml version="1.0" encoding="UTF-8"?>
<Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:iso:std:iso:20022:tech:xsd:pain.002.001.03">
<CstmrPmtStsRpt>
<GrpHdr>
<MsgId>0</MsgId>
<CreDtTm>2020-04-17T04:12:58</CreDtTm>
</GrpHdr>
<OrgnlGrpInfAndSts>
<OrgnlMsgId>0</OrgnlMsgId>
<OrgnlMsgNmId>pain.008.001.02</OrgnlMsgNmId>
<OrgnlNbOfTxs>2874</OrgnlNbOfTxs>
<OrgnlCtrlSum>0.59</OrgnlCtrlSum>
<StsRsnInf>
<Rsn>
<Prtry>M001</Prtry>
</Rsn>
</StsRsnInf>
<NbOfTxsPerSts>
<DtldNbOfTxs>1565</DtldNbOfTxs>
<DtldSts>RJCT</DtldSts>
<DtldCtrlSum>74317.84</DtldCtrlSum>
</NbOfTxsPerSts>
</OrgnlGrpInfAndSts>
<OrgnlPmtInfAndSts>
<OrgnlPmtInfId>01-10-1-RCUR</OrgnlPmtInfId>
<OrgnlNbOfTxs>2874</OrgnlNbOfTxs>
<OrgnlCtrlSum>91756.59</OrgnlCtrlSum>
<StsRsnInf>
<Rsn>
<Prtry>L001</Prtry>
</Rsn>
</StsRsnInf>
<NbOfTxsPerSts>
<DtldNbOfTxs>1565</DtldNbOfTxs>
<DtldSts>RJCT</DtldSts>
<DtldCtrlSum>0.84</DtldCtrlSum>
</NbOfTxsPerSts>
<TxInfAndSts>
<StsId>301</StsId>
<OrgnlEndToEndId>01,FTR,2020,114175</OrgnlEndToEndId>
<StsRsnInf>
<Rsn>
<Cd>0000</Cd>
</Rsn>
</StsRsnInf>
<AcctSvcrRef>0</AcctSvcrRef>
<OrgnlTxRef>
<Amt>
<InstdAmt Ccy="EUR">9.24</InstdAmt>
</Amt>
<ReqdColltnDt>2020-04-16</ReqdColltnDt>
<CdtrSchmeId>
<Id>
<PrvtId>
<Othr>
<Id>0</Id>
</Othr>
</PrvtId>
</Id>
</CdtrSchmeId>
<PmtTpInf>
<SvcLvl>
<Cd>SEPA</Cd>
</SvcLvl>
<SeqTp>RCUR</SeqTp>
<CtgyPurp>
<Cd>WTER</Cd>
</CtgyPurp>
</PmtTpInf>
<PmtMtd>DD</PmtMtd>
<MndtRltdInf>
<MndtId>0</MndtId>
<DtOfSgntr>2010-10-18</DtOfSgntr>
</MndtRltdInf>
<RmtInf>
<Ustrd>Fatura Consumo Abastecimento de Agua</Ustrd>
</RmtInf>
<Dbtr>
<Nm>Joaquim Cunha Silva</Nm>
</Dbtr>
<DbtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</DbtrAgt>
<CdtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</CdtrAgt>
<Cdtr>
<Nm>0</Nm>
<PstlAdr>
<Ctry>PT</Ctry>
<AdrLine>0</AdrLine>
</PstlAdr>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</CdtrAcct>
</OrgnlTxRef>
</TxInfAndSts>
<TxInfAndSts>
<StsId>301</StsId>
<OrgnlEndToEndId>01,FTR,2020,114180</OrgnlEndToEndId>
<StsRsnInf>
<Rsn>
<Cd>0000</Cd>
</Rsn>
</StsRsnInf>
<AcctSvcrRef>0</AcctSvcrRef>
<OrgnlTxRef>
<Amt>
<InstdAmt Ccy="EUR">9.90</InstdAmt>
</Amt>
<ReqdColltnDt>2020-04-16</ReqdColltnDt>
<CdtrSchmeId>
<Id>
<PrvtId>
<Othr>
<Id>0</Id>
</Othr>
</PrvtId>
</Id>
</CdtrSchmeId>
<PmtTpInf>
<SvcLvl>
<Cd>SEPA</Cd>
</SvcLvl>
<SeqTp>RCUR</SeqTp>
<CtgyPurp>
<Cd>WTER</Cd>
</CtgyPurp>
</PmtTpInf>
<PmtMtd>DD</PmtMtd>
<MndtRltdInf>
<MndtId>0</MndtId>
<DtOfSgntr>2011-08-19</DtOfSgntr>
</MndtRltdInf>
<RmtInf>
<Ustrd>Fatura Consumo Abastecimento de Agua</Ustrd>
</RmtInf>
<Dbtr>
<Nm>Teresa Jesus Silva Cerqueira</Nm>
</Dbtr>
<DbtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</DbtrAgt>
<CdtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</CdtrAgt>
<Cdtr>
<Nm>0</Nm>
<PstlAdr>
<Ctry>PT</Ctry>
<AdrLine>0</AdrLine>
</PstlAdr>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</CdtrAcct>
</OrgnlTxRef>
</TxInfAndSts>
<TxInfAndSts>
<StsId>301</StsId>
<OrgnlEndToEndId>01,FTR,2020,114169</OrgnlEndToEndId>
<StsRsnInf>
<Rsn>
<Cd>0000</Cd>
</Rsn>
</StsRsnInf>
<AcctSvcrRef>0</AcctSvcrRef>
<OrgnlTxRef>
<Amt>
<InstdAmt Ccy="EUR">8.23</InstdAmt>
</Amt>
<ReqdColltnDt>2020-04-16</ReqdColltnDt>
<CdtrSchmeId>
<Id>
<PrvtId>
<Othr>
<Id>0</Id>
</Othr>
</PrvtId>
</Id>
</CdtrSchmeId>
<PmtTpInf>
<SvcLvl>
<Cd>SEPA</Cd>
</SvcLvl>
<SeqTp>RCUR</SeqTp>
<CtgyPurp>
<Cd>WTER</Cd>
</CtgyPurp>
</PmtTpInf>
<PmtMtd>DD</PmtMtd>
<MndtRltdInf>
<MndtId>0</MndtId>
<DtOfSgntr>2014-10-30</DtOfSgntr>
</MndtRltdInf>
<RmtInf>
<Ustrd>Fatura Consumo Abastecimento de Agua</Ustrd>
</RmtInf>
<Dbtr>
<Nm>Manuel Fernando Rocha Braga</Nm>
</Dbtr>
<DbtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</DbtrAgt>
<CdtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</CdtrAgt>
<Cdtr>
<Nm>0</Nm>
<PstlAdr>
<Ctry>PT</Ctry>
<AdrLine>0</AdrLine>
</PstlAdr>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</CdtrAcct>
</OrgnlTxRef>
</TxInfAndSts>
<TxInfAndSts>
<StsId>301</StsId>
<OrgnlEndToEndId>01,FTR,2020,114173</OrgnlEndToEndId>
<StsRsnInf>
<Rsn>
<Cd>0000</Cd>
</Rsn>
</StsRsnInf>
<AcctSvcrRef>0</AcctSvcrRef>
<OrgnlTxRef>
<Amt>
<InstdAmt Ccy="EUR">5.42</InstdAmt>
</Amt>
<ReqdColltnDt>2020-04-16</ReqdColltnDt>
<CdtrSchmeId>
<Id>
<PrvtId>
<Othr>
<Id>0</Id>
</Othr>
</PrvtId>
</Id>
</CdtrSchmeId>
<PmtTpInf>
<SvcLvl>
<Cd>SEPA</Cd>
</SvcLvl>
<SeqTp>RCUR</SeqTp>
<CtgyPurp>
<Cd>WTER</Cd>
</CtgyPurp>
</PmtTpInf>
<PmtMtd>DD</PmtMtd>
<MndtRltdInf>
<MndtId>0</MndtId>
<DtOfSgntr>2008-10-20</DtOfSgntr>
</MndtRltdInf>
<RmtInf>
<Ustrd>Fatura Consumo Abastecimento de Agua</Ustrd>
</RmtInf>
<Dbtr>
<Nm>Manuel Goncalves Amorim</Nm>
</Dbtr>
<DbtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</DbtrAgt>
<CdtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</CdtrAgt>
<Cdtr>
<Nm>0</Nm>
<PstlAdr>
<Ctry>PT</Ctry>
<AdrLine>0</AdrLine>
</PstlAdr>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</CdtrAcct>
</OrgnlTxRef>
</TxInfAndSts>
<TxInfAndSts>
<StsId>301</StsId>
<OrgnlEndToEndId>01,FTR,2020,114174</OrgnlEndToEndId>
<StsRsnInf>
<Rsn>
<Cd>0000</Cd>
</Rsn>
</StsRsnInf>
<AcctSvcrRef>0</AcctSvcrRef>
<OrgnlTxRef>
<Amt>
<InstdAmt Ccy="EUR">9.24</InstdAmt>
</Amt>
<ReqdColltnDt>2020-04-16</ReqdColltnDt>
<CdtrSchmeId>
<Id>
<PrvtId>
<Othr>
<Id>0</Id>
</Othr>
</PrvtId>
</Id>
</CdtrSchmeId>
<PmtTpInf>
<SvcLvl>
<Cd>SEPA</Cd>
</SvcLvl>
<SeqTp>RCUR</SeqTp>
<CtgyPurp>
<Cd>WTER</Cd>
</CtgyPurp>
</PmtTpInf>
<PmtMtd>DD</PmtMtd>
<MndtRltdInf>
<MndtId>0</MndtId>
<DtOfSgntr>2008-10-20</DtOfSgntr>
</MndtRltdInf>
<RmtInf>
<Ustrd>Fatura Consumo Abastecimento de Agua</Ustrd>
</RmtInf>
<Dbtr>
<Nm>Manuel Antonio Goncalves Oliveira</Nm>
</Dbtr>
<DbtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</DbtrAgt>
<CdtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</CdtrAgt>
<Cdtr>
<Nm>0</Nm>
<PstlAdr>
<Ctry>PT</Ctry>
<AdrLine>0</AdrLine>
</PstlAdr>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</CdtrAcct>
</OrgnlTxRef>
</TxInfAndSts>
<TxInfAndSts>
<StsId>301</StsId>
<OrgnlEndToEndId>01,FTR,2020,114165</OrgnlEndToEndId>
<StsRsnInf>
<Rsn>
<Cd>0000</Cd>
</Rsn>
</StsRsnInf>
<AcctSvcrRef>0</AcctSvcrRef>
<OrgnlTxRef>
<Amt>
<InstdAmt Ccy="EUR">5.42</InstdAmt>
</Amt>
<ReqdColltnDt>2020-04-16</ReqdColltnDt>
<CdtrSchmeId>
<Id>
<PrvtId>
<Othr>
<Id>0</Id>
</Othr>
</PrvtId>
</Id>
</CdtrSchmeId>
<PmtTpInf>
<SvcLvl>
<Cd>SEPA</Cd>
</SvcLvl>
<SeqTp>RCUR</SeqTp>
<CtgyPurp>
<Cd>WTER</Cd>
</CtgyPurp>
</PmtTpInf>
<PmtMtd>DD</PmtMtd>
<MndtRltdInf>
<MndtId>0</MndtId>
<DtOfSgntr>2015-08-18</DtOfSgntr>
</MndtRltdInf>
<RmtInf>
<Ustrd>Fatura Consumo Abastecimento de Agua</Ustrd>
</RmtInf>
<Dbtr>
<Nm>Manuel Fernandes Neiva</Nm>
</Dbtr>
<DbtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</DbtrAgt>
<CdtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</CdtrAgt>
<Cdtr>
<Nm>0</Nm>
<PstlAdr>
<Ctry>PT</Ctry>
<AdrLine>R 0</AdrLine>
</PstlAdr>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</CdtrAcct>
</OrgnlTxRef>
</TxInfAndSts>
<TxInfAndSts>
<StsId>301</StsId>
<OrgnlEndToEndId>01,FTR,2020,114242</OrgnlEndToEndId>
<StsRsnInf>
<Rsn>
<Cd>0000</Cd>
</Rsn>
</StsRsnInf>
<AcctSvcrRef>0</AcctSvcrRef>
<OrgnlTxRef>
<Amt>
<InstdAmt Ccy="EUR">5.42</InstdAmt>
</Amt>
<ReqdColltnDt>2020-04-16</ReqdColltnDt>
<CdtrSchmeId>
<Id>
<PrvtId>
<Othr>
<Id>0</Id>
</Othr>
</PrvtId>
</Id>
</CdtrSchmeId>
<PmtTpInf>
<SvcLvl>
<Cd>SEPA</Cd>
</SvcLvl>
<SeqTp>RCUR</SeqTp>
<CtgyPurp>
<Cd>WTER</Cd>
</CtgyPurp>
</PmtTpInf>
<PmtMtd>DD</PmtMtd>
<MndtRltdInf>
<MndtId>0</MndtId>
<DtOfSgntr>2008-10-20</DtOfSgntr>
</MndtRltdInf>
<RmtInf>
<Ustrd>Fatura Consumo Abastecimento de Agua</Ustrd>
</RmtInf>
<Dbtr>
<Nm>Manuel Jose Costa Goncalves</Nm>
</Dbtr>
<DbtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</DbtrAgt>
<CdtrAgt>
<FinInstnId>
<BIC>0</BIC>
</FinInstnId>
</CdtrAgt>
<Cdtr>
<Nm>0</Nm>
<PstlAdr>
<Ctry>PT</Ctry>
<AdrLine>0</AdrLine>
</PstlAdr>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>0</IBAN>
</Id>
</CdtrAcct>
</OrgnlTxRef>
</TxInfAndSts>
</OrgnlPmtInfAndSts>
</CstmrPmtStsRpt>
</Document>
And my SQL code is:
WITH rs (xmlData) AS
(
SELECT TRY_CAST(BulkColumn AS XML)
FROM OPENROWSET(BULK N'c:\temp\agu\bancos.xml', SINGLE_BLOB) AS x
)
-- INSERT INTO dbo.Import_Oase (StationID, TransactionTimestamp, TicketNumber, Mileage)
SELECT c.value('(StsId/text())[1]', 'VARCHAR(50)') AS StationID
, c.value('(OrgnlEndToEndId/text())[1]', 'varchar(50)') AS TransactionTimestamp
,c.value('(StsRsnInf/Rsn/Cd/text())[1]','varchar(50)') as umdois
,c.value('(AcctSvcrRef/text())[1]','varchar(50)') as Iban
,c.value('(OrgnlTxRef/Amt/InstdAmt/text())[1]','varchar(50)') as asjds
,c.value('(OrgnlTxRef/Dbtr/Nm/text())[1]','varchar(50)') as asjds
,c.value('(OrgnlTxRef/DbtrAcct/Id/IBAN/text())[1]','varchar(50)') as iban2
/*,c.value('OrgnlTxRef/Dbtr/Nm/text())[1]','varchar(50)') as dinheiro*/
/* , c.value('(TransactionInfo/TicketNumber/text())[1]', 'VARCHAR(50)') AS TicketNumber
, c.value('(TransactionInfo/CustomerInput/Mileage/text())[1]', 'INT') AS Mileage*/
FROM rs
CROSS APPLY xmlData.nodes('/Document/CstmrPmtStsRpt/OrgnlPmtInfAndSts/TxInfAndSts') AS t(c);
Can you help me?
答案1
得分: 2
请尝试以下解决方案。
您的XML具有默认命名空间。需要通过**XMLNAMESPACES()**子句进行特殊处理。
SQL #1
WITH XMLNAMESPACES(DEFAULT 'urn:iso:std:iso:20022:tech:xsd:pain.002.001.03')
, rs (xmlData) AS
(
SELECT TRY_CAST(BulkColumn AS XML)
FROM OPENROWSET(BULK N'c:\temp\agu\bancos.xml', SINGLE_BLOB) AS x
)
-- INSERT INTO dbo.Import_Oase (StationID, TransactionTimestamp, TicketNumber, Mileage)
SELECT c.value('(StsId/text())[1]', 'VARCHAR(50)') AS StationID
, c.value('(OrgnlEndToEndId/text())[1]', 'varchar(50)') AS TransactionTimestamp
,c.value('(StsRsnInf/Rsn/Cd/text())[1]','varchar(50)') as umdois
,c.value('(AcctSvcrRef/text())[1]','varchar(50)') as Iban
,c.value('(OrgnlTxRef/Amt/InstdAmt/text())[1]','varchar(50)') as asjds
,c.value('(OrgnlTxRef/Dbtr/Nm/text())[1]','varchar(50)') as asjds
,c.value('(OrgnlTxRef/DbtrAcct/Id/IBAN/text())[1]','varchar(50)') as iban2
FROM rs
CROSS APPLY xmlData.nodes('/Document/CstmrPmtStsRpt/OrgnlPmtInfAndSts/TxInfAndSts') AS t(c);
SQL #2
DECLARE @tbl TABLE(
ID INT IDENTITY(1, 1) PRIMARY KEY,
XmlColumn XML
);
INSERT INTO @tbl(XmlColumn)
SELECT * FROM OPENROWSET(BULK N'c:\temp\agu\bancos.xml', SINGLE_BLOB) AS x;
WITH XMLNAMESPACES(DEFAULT 'urn:iso:std:iso:20022:tech:xsd:pain.002.001.03')
SELECT c.value('(StsId/text())[1]', 'VARCHAR(50)') AS StationID
, c.value('(OrgnlEndToEndId/text())[1]', 'varchar(50)') AS TransactionTimestamp
, c.value('(StsRsnInf/Rsn/Cd/text())[1]','varchar(50)') as umdois
, c.value('(AcctSvcrRef/text())[1]','varchar(50)') as Iban
, c.value('(OrgnlTxRef/Amt/InstdAmt/text())[1]','varchar(50)') as asjds
, c.value('(OrgnlTxRef/Dbtr/Nm/text())[1]','varchar(50)') as asjds
, c.value('(OrgnlTxRef/DbtrAcct/Id/IBAN/text())[1]','varchar(50)') as iban2
FROM @tbl
CROSS APPLY XmlColumn.nodes('/Document/CstmrPmtStsRpt/OrgnlPmtInfAndSts/TxInfAndSts') AS t(c);
输出
| StationID | TransactionTimestamp | umdois | Iban | asjds | asjds | iban2 |
|---|---|---|---|---|---|---|
| 301 | 01,FTR,2020,114175 | 0000 | 0 | 9.24 | Joaquim Cunha Silva | 0 |
| 301 | 01,FTR,2020,114180 | 0000 | 0 | 9.90 | Teresa Jesus Silva Cerqueira | 0 |
| 301 | 01,FTR,2020,114169 | 0000 | 0 | 8.23 | Manuel Fernando Rocha Braga | 0 |
| 301 | 01,FTR,2020,114173 | 0000 | 0 | 5.42 | Manuel Goncalves Amorim | 0 |
| 301 | 01,FTR,2020,114174 | 0000 | 0 | 9.24 | Manuel Antonio Goncalves Oliveira | 0 |
| 301 | 01,FTR,2020,114165 | 0000 | 0 | 5.42 | Manuel Fernandes Neiva | 0 |
| 301 | 01,FTR,2020,114242 | 0000 | 0 | 5.42 | Manuel Jose Costa Goncalves | 0 |
英文:
Please try the following solution.
Your XML has a default namespace. It needs a special handling via XMLNAMESPACES() clause.
SQL #1
WITH XMLNAMESPACES(DEFAULT 'urn:iso:std:iso:20022:tech:xsd:pain.002.001.03')
, rs (xmlData) AS
(
SELECT TRY_CAST(BulkColumn AS XML)
FROM OPENROWSET(BULK N'c:\temp\agu\bancos.xml', SINGLE_BLOB) AS x
)
-- INSERT INTO dbo.Import_Oase (StationID, TransactionTimestamp, TicketNumber, Mileage)
SELECT c.value('(StsId/text())[1]', 'VARCHAR(50)') AS StationID
, c.value('(OrgnlEndToEndId/text())[1]', 'varchar(50)') AS TransactionTimestamp
,c.value('(StsRsnInf/Rsn/Cd/text())[1]','varchar(50)') as umdois
,c.value('(AcctSvcrRef/text())[1]','varchar(50)') as Iban
,c.value('(OrgnlTxRef/Amt/InstdAmt/text())[1]','varchar(50)') as asjds
,c.value('(OrgnlTxRef/Dbtr/Nm/text())[1]','varchar(50)') as asjds
,c.value('(OrgnlTxRef/DbtrAcct/Id/IBAN/text())[1]','varchar(50)') as iban2
FROM rs
CROSS APPLY xmlData.nodes('/Document/CstmrPmtStsRpt/OrgnlPmtInfAndSts/TxInfAndSts') AS t(c);
SQL #2
Here is much more performant method. Give it a shot.
DECLARE @tbl TABLE(
ID INT IDENTITY(1, 1) PRIMARY KEY,
XmlColumn XML
);
INSERT INTO @tbl(XmlColumn)
SELECT * FROM OPENROWSET(BULK N'c:\temp\agu\bancos.xml', SINGLE_BLOB) AS x;
WITH XMLNAMESPACES(DEFAULT 'urn:iso:std:iso:20022:tech:xsd:pain.002.001.03')
SELECT c.value('(StsId/text())[1]', 'VARCHAR(50)') AS StationID
, c.value('(OrgnlEndToEndId/text())[1]', 'varchar(50)') AS TransactionTimestamp
, c.value('(StsRsnInf/Rsn/Cd/text())[1]','varchar(50)') as umdois
, c.value('(AcctSvcrRef/text())[1]','varchar(50)') as Iban
, c.value('(OrgnlTxRef/Amt/InstdAmt/text())[1]','varchar(50)') as asjds
, c.value('(OrgnlTxRef/Dbtr/Nm/text())[1]','varchar(50)') as asjds
, c.value('(OrgnlTxRef/DbtrAcct/Id/IBAN/text())[1]','varchar(50)') as iban2
FROM @tbl
CROSS APPLY XmlColumn.nodes('/Document/CstmrPmtStsRpt/OrgnlPmtInfAndSts/TxInfAndSts') AS t(c);
Output
| StationID | TransactionTimestamp | umdois | Iban | asjds | asjds | iban2 |
|---|---|---|---|---|---|---|
| 301 | 01,FTR,2020,114175 | 0000 | 0 | 9.24 | Joaquim Cunha Silva | 0 |
| 301 | 01,FTR,2020,114180 | 0000 | 0 | 9.90 | Teresa Jesus Silva Cerqueira | 0 |
| 301 | 01,FTR,2020,114169 | 0000 | 0 | 8.23 | Manuel Fernando Rocha Braga | 0 |
| 301 | 01,FTR,2020,114173 | 0000 | 0 | 5.42 | Manuel Goncalves Amorim | 0 |
| 301 | 01,FTR,2020,114174 | 0000 | 0 | 9.24 | Manuel Antonio Goncalves Oliveira | 0 |
| 301 | 01,FTR,2020,114165 | 0000 | 0 | 5.42 | Manuel Fernandes Neiva | 0 |
| 301 | 01,FTR,2020,114242 | 0000 | 0 | 5.42 | Manuel Jose Costa Goncalves | 0 |
答案2
得分: -1
你可以尝试使用PowerShell来查看是否更快。使用Invoke-sqlcmd命令：https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps
使用以下代码来解析XML：
using assembly System.Xml.Linq
$inputFilename = "c:\temp\test.xml"
$doc = [System.Xml.Linq.XDocument]::Load($inputFilename)
$ns = $doc.Root.GetDefaultNamespace()
$TxInfAndSts = $doc.Descendants($ns + 'TxInfAndSts')
$table = [System.Collections.ArrayList]::new()
foreach($TxInfAndSt in $TxInfAndSts)
{
$StationID = $TxInfAndSt.Descendants($ns + 'StsId')[0].Value
$TransactionTimestamp = $TxInfAndSt.Descendants($ns + 'OrgnlEndToEndId')[0].Value
$umdois = $TxInfAndSt.Descendants($ns + 'Cd')[0].Value
$Iban = $TxInfAndSt.Descendants($ns + 'AcctSvcrRef')[0].Value
$asjds = $TxInfAndSt.Descendants($ns + 'InstdAmt')[0].Value
$iban2 = $TxInfAndSt.Descendants($ns + 'IBAN')[0].Value
$newRow = [pscustomobject]@{
StationID = $StationID
TransactionTimestamp = $TransactionTimestamp
umdois = $umdois
Iban = $Iban
asjds = $asjds
iban2 = $iban2
}
$table.Add($newRow)
}
$table
以上是你提供的代码的翻译部分。
英文:
You can try with powershell to see if it is faster. Usse Invoke-sqlcmd : https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps
Use following to parse the xml
<!-- begin snippet: js hide: false console: true babel: false -->
using assembly System.Xml.Linq
$inputFilename = "c:\temp\test.xml"
$doc = [System.Xml.Linq.XDocument]::Load($inputFilename)
$ns = $doc.Root.GetDefaultNamespace()
$TxInfAndSts = $doc.Descendants($ns + 'TxInfAndSts')
$table = [System.Collections.ArrayList]::new()
foreach($TxInfAndSt in $TxInfAndSts)
{
$StationID = $TxInfAndSt.Descendants($ns + 'StsId')[0].Value
$TransactionTimestamp = $TxInfAndSt.Descendants($ns + 'OrgnlEndToEndId')[0].Value
$umdois = $TxInfAndSt.Descendants($ns + 'Cd')[0].VAlue
$Iban = $TxInfAndSt.Descendants($ns + 'AcctSvcrRef')[0].Value
$asjds = $TxInfAndSt.Descendants($ns + 'InstdAmt')[0].Value
$iban2 = $TxInfAndSt.Descendants($ns + 'IBAN')[0].Value
$newRow = [pscustomobject]@{
StationID = $StationID
TransactionTimestamp = $TransactionTimestamp
umdois = $umdois
Iban = $Iban
asjds = $asjds
iban2 = $iban2
}
$table.Add($newRow)
}
$table
<!-- end snippet -->
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论