Parse selected values from XML in SQL
I have the following XML file (I put here only a short scheme), it’s a SEPA XML bank statement. I’m not familiar with parsing XML files, my next move will be inserting and comparing to data stored in the SQL databases for error checks. Sadly, I know what to do next, don’t know how to make progress with my first step. All I need is to create a table to select values of 2 attributes from a file stored at a particular place
<?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> If the XML structure was simpler, for example like this…
<?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> …then I’d use this query to select requested attributes Amt and AddtlNtryInf and it works perfectly
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); But don’t know how to deal with that more complicated one. I’ve tried something like this and several similar attempts but I failed because it doesn’t select anything, the result is nothing
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); Can’t figure out what to do with that CROSS APPLY. Thank you very much for any suggestions or improvements, you’re doing a great job
You were almost there.
(1) The XML file has a default namespace, and it needs a special treatment via XMLNAMESPACES clause.
(2) The Amt element probably has a numeric value so you could use DECIMAL(x,y) data type. But I kept the NVARCHAR(255) to match the obfuscated XML file example.
(3) The SQL below is using .value() method without unnecessary .query() method.
(4) It is a good practice to use elementName/text() technique for performance reasons. It is MS SQL Server specific peculiarity.
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; 
Something like this:
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) 
