0

I am trying to read a physical xml file with below schema

    <Document>
<CstmrDrctDbtInitn>
<PmtInf>
  <DrctDbtTxInf>

    <InstdAmt Ccy="EUR">1</InstdAmt>

   <DrctDbtTx>
      <MndtRltdInf>
        <MndtId>umr</MndtId>
        <DtOfSgntr>2020-04-07</DtOfSgntr>
      </MndtRltdInf>
    </DrctDbtTx>
   <Dbtr>
      <Nm>Akings</Nm>
    </Dbtr>

   <DbtrAcct>
      <Id>
        <IBAN>123456789132456789523</IBAN>
      </Id>
    </DbtrAcct>

  </DrctDbtTxInf>   
  </PmtInf>
      </CstmrDrctDbtInitn>
    </Document>

By using the below query:

DECLARE @xml XML
SELECT @xml = BulkColumn
FROM OPENROWSET(BULK 'E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLELEMOS\MSSQL\Backup\test.xml', SINGLE_BLOB) x
SELECT
  t.c.query('InstdAmt').value('.', 'NVARCHAR(50)'),
   t.c.query('Dbtr/Nm').value('.', 'NVARCHAR(50)')  
FROM @xml.nodes('*:Document/*:CstmrDrctDbtInitn/*:PmtInf/*:DrctDbtTxInf') t(c)

But I am getting blank results every time I run it, any idea of what could I be missing here?

Improve this question
9
  • 1
    The root node of the XML is Dcoument but you specified Document in the query. Change the XML or query to be consistent. Commented Jun 5, 2020 at 11:09
  • Actually I have changed it and still blanks Commented Jun 5, 2020 at 11:14
  • 1
    I ran your query after changing the element name to Dcoument and it returned "1 Akings". Commented Jun 5, 2020 at 11:25
  • Do you have any namespaces in your real XML? Commented Jun 5, 2020 at 11:30
  • yes there is namespaces, do I need to declare that somewhere else ? Commented Jun 5, 2020 at 11:33

1 Answer 1

Reset to default
1

You can easily check if reading the XML's content into your variable is working as expected. Assuming the XML is finally read successfully, this should work:

DECLARE @xml XML =
N'<Document>
  <CstmrDrctDbtInitn>
    <PmtInf>
      <DrctDbtTxInf>
        <InstdAmt Ccy="EUR">1</InstdAmt>
        <DrctDbtTx>
          <MndtRltdInf>
            <MndtId>umr</MndtId>
            <DtOfSgntr>2020-04-07</DtOfSgntr>
          </MndtRltdInf>
        </DrctDbtTx>
        <Dbtr>
          <Nm>Akings</Nm>
        </Dbtr>
        <DbtrAcct>
          <Id>
            <IBAN>123456789132456789523</IBAN>
          </Id>
        </DbtrAcct>
      </DrctDbtTxInf>
    </PmtInf>
  </CstmrDrctDbtInitn>
</Document>';

--reading the values directly (if non-repeated)

SELECT @xml.value('(/Document/CstmrDrctDbtInitn/PmtInf/DrctDbtTxInf/InstdAmt/text())[1]','int') AS InstAmt
      ,@xml.value('(/Document/CstmrDrctDbtInitn/PmtInf/DrctDbtTxInf/Dbtr/Nm/text())[1]','nvarchar(max)') AS InstAmt;

--reading - as you do it - with .nodes()

SELECT t.c.value('(InstdAmt/text())[1]','int') AS InstAmt
      ,t.c.value('(Dbtr/Nm/text())[1]','nvarchar(max)') AS InstAmt
FROM @xml.nodes('/Document/CstmrDrctDbtInitn/PmtInf/DrctDbtTxInf') t(c);

Your own code is working too, actually...

The usage of the namespace wildcard (*:) and your blank results let me think, that the XML you are showing us is just a part of the whole thing. It might be enough (if the element <Document> does not appear in other places) to use a deep search with a doubled // at the beginning (=> start with '//*:Document').

UPDATE: If there is a (default) namespace

From your comments above I take, that there is a default namespace involved, just try this:

DECLARE @xml XML =
N'<Document xmlns="someUri">   <!-- adding a default namespace here -->
  <CstmrDrctDbtInitn>
    <PmtInf>
      <DrctDbtTxInf>
        <InstdAmt Ccy="EUR">1</InstdAmt>
        <DrctDbtTx>
          <MndtRltdInf>
            <MndtId>umr</MndtId>
            <DtOfSgntr>2020-04-07</DtOfSgntr>
          </MndtRltdInf>
        </DrctDbtTx>
        <Dbtr>
          <Nm>Akings</Nm>
        </Dbtr>
        <DbtrAcct>
          <Id>
            <IBAN>123456789132456789523</IBAN>
          </Id>
        </DbtrAcct>
      </DrctDbtTxInf>
    </PmtInf>
  </CstmrDrctDbtInitn>
</Document>';

--reading the values needs to declare the namespace(s)

WITH XMLNAMESPACES(DEFAULT 'someUri')
SELECT @xml.value('(/Document/CstmrDrctDbtInitn/PmtInf/DrctDbtTxInf/InstdAmt/text())[1]','int') AS InstAmt
      ,@xml.value('(/Document/CstmrDrctDbtInitn/PmtInf/DrctDbtTxInf/Dbtr/Nm/text())[1]','nvarchar(max)') AS InstAmt;

Using the wildcard *: will work too, but makes you prone to errors, if there are name clashes in more complex sources.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.