I have an XML document with the default namespace urn:iso:std:iso:20022:tech:xsd:camt.053.001.02
and I believe I have a need to use a cursor to iterate across a repeated segment.
Here is a very much simplified version of the file:
<Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
<BkToCstmrStmt>
<GrpHdr>...</GrpHdr>
<Stmt>
<Id>1</Id>
<CreDtTm>2015-06-23T03:25:08.688+01:00</CreDtTm>
</Stmt>
<Stmt>
<Id>2</Id>
<CreDtTm>2015-06-23T03:25:09.000+01:00</CreDtTm>
</Stmt>
<Stmt>
<Id>3</Id>
<CreDtTm>2015-06-23T03:25:10.051+01:00</CreDtTm>
</Stmt>
</BkToCstmrStmt>
</Document>
I am building upon the advice given in this answer for SQL 2012 - iterate through an XML list (better alternative to a WHILE loop).
Without the default xmlns
I would be able to declare a cursor to iterate across the stmt
sections like this:
declare c cursor fast_forward for
select
s.c.value('(Id/text())[1]', 'integer') as Id,
s.c.value('(CreDtTm/text())[1]', 'datetime2(3)') as CreDtTm
from @XML_In.nodes('Document/BkToCstmrStmt') as b(c)
outer apply b.c.nodes('Stmt') as s(c)
which would return this result set:
Id CreDtTm
1 2015-06-23 02:25:08.688
2 2015-06-23 02:25:09.000
3 2015-06-23 02:25:10.051
If I were using a plain select
statement I could declare the default namespace for the query like this (given @XML_In
is a string representing the XML document):
with xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')
select
s.c.value('(Id/text())[1]', 'integer') as Id,
s.c.value('(CreDtTm/text())[1]', 'datetime2(3)') as CreDtTm
from @XML_In.nodes('Document/BkToCstmrStmt') as b(c)
outer apply b.c.nodes('Stmt') as s(c)
How can I combine the use of a cursor with the need to query within an explicit default namespace?
Taking the example from your linked questions, have you tried a simple combination of the two?
DECLARE @XML_in XML = '<Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
<BkToCstmrStmt>
<GrpHdr>...</GrpHdr>
<Stmt>
<Id>1</Id>
<CreDtTm>2015-06-23T03:25:08.688+01:00</CreDtTm>
</Stmt>
<Stmt>
<Id>2</Id>
<CreDtTm>2015-06-23T03:25:09.000+01:00</CreDtTm>
</Stmt>
<Stmt>
<Id>3</Id>
<CreDtTm>2015-06-23T03:25:10.051+01:00</CreDtTm>
</Stmt>
</BkToCstmrStmt>
</Document>'
DECLARE cur CURSOR FAST_FORWARD
FOR
with xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')
SELECT s.c.value('(Id/text())[1]', 'integer') AS Id
,s.c.value('(CreDtTm/text())[1]', 'datetime2(3)') AS CreDtTm
FROM @XML_In.nodes('Document/BkToCstmrStmt') AS b(c)
OUTER APPLY b.c.nodes('Stmt') AS s(c)
declare @Id int
declare @CreDtTm datetime2(3)
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur
INTO @Id
,@CreDtTm
IF @@fetch_status <> 0
BREAK
-- Do whatever you like with your cursor
select @Id, @CreDtTm
END
CLOSE cur
DEALLOCATE cur
Results
----------- ---------------------------
1 2015-06-23 02:25:08.688
(1 row(s) affected)
----------- ---------------------------
2 2015-06-23 02:25:09.000
(1 row(s) affected)
----------- ---------------------------
3 2015-06-23 02:25:10.051
(1 row(s) affected)