从sql中的xml解析选定的值

vnjpjtjt 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(357)

我有以下xml文件（我在这里只放了一个简短的方案），它是一个sepa xml银行对账单。我不熟悉解析xml文件，下一步将插入并比较存储在sql数据库中的数据以进行错误检查。可悲的是，我知道下一步该做什么，不知道如何迈出第一步。我只需要创建一个表，从存储在特定位置的文件中选择2个属性的值

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
  <BkToCstmrStmt>
    <GrpHdr>
      ..........
    </GrpHdr>
    <Stmt>
      <Ntry>
        <Amt Ccy="EUR">RequestedAmount1</Amt>
        <AddtlNtryInf>RequestedInfo1</AddtlNtryInf>
      </Ntry>
      <Ntry>
        <Amt Ccy="EUR">RequestedAmount2</Amt>
        <AddtlNtryInf>RequestedInfo2</AddtlNtryInf>
      </Ntry>
      <Ntry>
        <Amt Ccy="EUR">RequestedAmount3</Amt>
        <AddtlNtryInf>RequestedInfo3</AddtlNtryInf>
      </Ntry>
    </Stmt>
  </BkToCstmrStmt>
</Document>

如果xml结构更简单，例如这样。。。

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Ntry>
  <Amt Ccy="EUR">RequestedAmount1</Amt>
  <AddtlNtryInf>RequestedInfo1</AddtlNtryInf>
</Ntry>
<Ntry>
  <Amt Ccy="EUR">RequestedAmount2</Amt>
  <AddtlNtryInf>RequestedInfo2</AddtlNtryInf>
</Ntry>
<Ntry>
  <Amt Ccy="EUR">RequestedAmount3</Amt>
  <AddtlNtryInf>RequestedInfo3</AddtlNtryInf>
</Ntry>

…然后我将使用此查询来选择请求的属性amt和addtlntryinf，它可以完美地工作

SELECT
   MY_XML.Ntry.query('Amt').value('.', 'NVARCHAR(255)') AS Amt,
   MY_XML.Ntry.query('AddtlNtryInf').value('.', 'NVARCHAR(255)') AS AddtlNtryInf
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'C:\tmp\TestSqlSimple.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('Ntry') AS MY_XML (Ntry);

但我不知道如何处理这个更复杂的问题。我也尝试过类似的方法，但我失败了，因为它没有选择任何东西，结果是什么都没有

SELECT
   MY_XML.Ntry.query('Amt').value('.', 'NVARCHAR(255)') AS Amt,
   MY_XML.Ntry.query('AddtlNtryInf').value('.', 'NVARCHAR(255)') AS AddtlNtryInf
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'C:\tmp\TestSqlSimple.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('/Document/BkToCstmrStmt/Stmt/Ntry') AS MY_XML (Ntry);

不知道该怎么处理交叉申请。非常感谢您的任何建议或改进，您做得很好

sql sql-server xml xml-parsing

来源：https://stackoverflow.com/questions/62734117/parse-selected-values-from-xml-in-sql

2条答案

按热度按时间

cuxqih211#

你差点就到了。
（1） xml文件有一个默认的名称空间，需要通过 XMLNAMESPACES 条款。
（2） amt元素可能有一个数值，因此可以使用 DECIMAL(x,y) 数据类型。但我保留了 NVARCHAR(255) 匹配模糊的xml文件示例。
（3）下面的sql正在使用 .value() 方法而不是不必要的 .query() 方法。
（4）这是一个很好的做法 elementName/text() 出于性能原因的技术。这是mssqlserver特有的特性。
sql语句

-- DDL and sample data population, start
DECLARE @tbl TABLE (
   ID INT IDENTITY PRIMARY KEY,
   Amt NVARCHAR(255),
   AddtlNtryInf NVARCHAR(255)
);
-- DDL and sample data population, end

;WITH XMLNAMESPACES (DEFAULT 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02') 
    , XmlFile (xmlData) AS
(
   SELECT TRY_CAST(BulkColumn AS XML) 
   FROM OPENROWSET(BULK 'e:\Temp\TestSqlSimple.xml', CODEPAGE = '65001', SINGLE_BLOB) AS x
)
INSERT INTO @tbl (Amt, AddtlNtryInf)
SELECT c.value('(Amt/text())[1]', 'NVARCHAR(255)') AS Amt
    , c.value('(AddtlNtryInf/text())[1]', 'NVARCHAR(255)') AS AddtlNtryInf
FROM XmlFile CROSS APPLY xmlData.nodes('/Document/BkToCstmrStmt/Stmt/Ntry') AS t(c);

-- test
SELECT * FROM @tbl;

赞(0）回复(0）举报 2021-07-26

p4tfgftt2#

像这样：

declare @doc xml = '<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
  <BkToCstmrStmt>
    <GrpHdr>
      ..........
    </GrpHdr>
    <Stmt>
      <Ntry>
        <Amt Ccy="EUR">RequestedAmount1</Amt>
        <AddtlNtryInf>RequestedInfo1</AddtlNtryInf>
      </Ntry>
      <Ntry>
        <Amt Ccy="EUR">RequestedAmount2</Amt>
        <AddtlNtryInf>RequestedInfo2</AddtlNtryInf>
      </Ntry>
      <Ntry>
        <Amt Ccy="EUR">RequestedAmount3</Amt>
        <AddtlNtryInf>RequestedInfo3</AddtlNtryInf>
      </Ntry>
    </Stmt>
  </BkToCstmrStmt>
 </Document>';

with xmlnamespaces (DEFAULT 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')  
select s.Stmt.value('(GrpHdr)[1]', 'varchar(200)') GrpHdr,
       n.Ntry.value('(Amt)[1]', 'varchar(200)') Amt,
       n.Ntry.value('(AddtlNtryInf)[1]', 'varchar(200)') AddtlNtryInf
from @doc.nodes('/Document/BkToCstmrStmt') s(Stmt)
outer apply s.Stmt.nodes('Stmt/Ntry') n(Ntry)

赞(0）回复(0）举报 2021-07-26

我来回答

从sql中的xml解析选定的值

2条答案

相关问题

热门标签

最新问答