How to read an XML file and validate with the data from a table

Question

I am in the creation of a functionality that allows loading XML files but before loading them I need to verify their content, I do not know if it is the correct way but I was thinking of something like first reading the XML to review its content and then validating it with the data that I have stored in a database table.

At the moment what I have developed so far is the loading of the XML file using AJAX and PHP as follows.

This is part of the form with which I load my XML files

<form action="" enctype="multipart/form-data">
          <div class="form-group row">
                        <label for="fileToUpload" class="col-sm-3 col-form-label"> File XML:</label>
              <div class="col-sm-8">
                <input type="file" name="fileToUpload" id="XmlToUpload" class="btn" accept=".xml" required>
              </div>
                    </div>
                    

          <div class="modal-footer">
            <button type="button" class="btn btn-secondary" data-dismiss="modal">Cancel</button>
            <button type="button" id="upload" class="btn btn-success">Upload Documents</button>
          </div>
      </form>

The following is the JQuery function with which I load the document and validate its format

$(document).ready(function(){
    $("#upload").on("click", function(){
        const ArchivoXml = $("#XmlToUpload").prop('files')[0];          

        
        if (ArchivoXml.type !== 'text/xml') {
            $('#XmlToUpload').val('');
            //$('#pdfToUpload').val('');
            alert('Please select the files with the correct format');
            return;
          }

        const datosForm = new FormData;

        datosForm.append("XmlToUpload", ArchivoXml);
       
        const filePath="server.php"
        //console.log(ArchivoXml);
        $.ajax({
            type:'POST',
            cache: false,
            contentType: false,
            processData: false,
            data: datosForm,
            url:filePath
        }).done(function(data){
            alert(data)
        }).fail(function(){
            alert('The file could not be loaded')
        });       
    });   
});

The following is the PHP code with which I indicate the path where it will be stored

server.php

<?php
    if(isset($_FILES["XmlToUpload"])){
        $archivo = $_FILES["XmlToUpload"]["name"];
        $ruta ="XML/";

        if(move_uploaded_file($_FILES["XmlToUpload"]["tmp_name"],$ruta.$archivo)){
            echo "Successfully uploaded";
        }else{
            echo "Error";
        }          
    }else {
        echo "A file was not selected";
    }
?>

As I mentioned at the beginning of the question, I need to validate the content of the XML and validate it with the information found in a database table.

The structure of the XML file will contain the following information:

<?xml version="1.0" encoding="UTF-8"?>
<cfdi:Voucher Version="3.3" Total="45264.13">
    <cfdi:Issuer Rfc="ABC123456AB1" Name="JOHN DOE"/>
     <cfdi:Concepts>
         <cfdi:Concept UnitValue="93.80">
        </cfdi:Concept>
    </cfdi:Concepts>
     <cfdi:Plugin>
        <tfd:Digital Version="1.1" />
    </cfdi:Plugin>
</cfdi:Voucher>

Here the data that I am interested in validating are Total="45264.13" and Rfc="ABC123456AB1"

In addition to this, I started the task of creating a Stored Procedure in SQLServer to obtain the information from the Database and thus compare with the XML data and the value of the fields of the table in the Database.

USE [dbtest]
GO
/****** Object:  StoredProcedure [dbo].[sptest]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sptest]
    
AS
BEGIN
    
    Select VTB.RFC as 'RFC',
           LRC.QTY as 'QTY',  
           PL.PRICE as 'Price',
           (PL.PRICE *LRC.QTY) * 1.16 as 'Total'
    from PACKING RC  
        inner join VTABLE VTB on VTB.COLUMN = RC.INVOICE
        inner join TRANS LRC on (LRC.PACKING = RC.RECID)
        inner join PURCH PL on (PL.NUMBER =LRC.NUM and PL.PURCH =RC.PURCH)      
END

I hope someone can guide me a bit with this functionality that I am creating.

Update:

With the aim of giving a much faster validation so that the data is equal to the content of the XML, now I have decided to obtain them in the front, that is, to show them in the same view where I load the XML as follows:

<div class="form-group row">
                        <label for="lblNombreVisita" class="col-sm-3 col-form-label">RFC:</label>
                        <div class="col-sm-8">
                          <input type="text" class="form-control" id="txtRfc" disabled>
                        </div>
                     </div>
           <div class="form-group row">
                        <label for="lblNombreVisita" class="col-sm-3 col-form-label">Total:</label>
                        <div class="col-sm-8">
                          <input type="text" class="form-control" id="txtTotal" disabled>
                        </div>
                     </div>

<form action="" enctype="multipart/form-data">
              <div class="form-group row">
                            <label for="fileToUpload" class="col-sm-3 col-form-label"> Archivo XML:</label>
                  <div class="col-sm-8">
                    <input type="file" name="fileToUpload" id="XmlToUpload" class="btn" accept=".xml" required>
                  </div>
                        </div>
                        

              <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-dismiss="modal">Cancelar</button>
                <button type="button" id="upload" class="btn btn-success">Subir Documentos</button>
              </div>
          </form>

Likewise, if someone finds how to validate them to be equal to the values ​​found in the database, it is also valid or if they can be directly validated with the values ​​that I am obtaining in the text fields that I just added, it is also valid.

Answer 1

Assuming the XML you shared is the actual XML passed to SQL Server, you are going to run into challenges with namespaces. They need to be declared in the XML and then again when querying for data.

Here's an example that returns the values for Total and Rfc as you have shared.

-- String holding the supplied XML.

DECLARE @my_xml varchar(MAX) = 
'<?xml version="1.0" encoding="UTF-8"?>
<cfdi:Voucher Version="3.3" Total="45264.13">
  <cfdi:Issuer Rfc="ABC123456AB1" Name="JOHN DOE"/>
  <cfdi:Concepts>
    <cfdi:Concept UnitValue="93.80"></cfdi:Concept>
  </cfdi:Concepts>
  <cfdi:Plugin>
    <tfd:Digital Version="1.1" />
  </cfdi:Plugin>
</cfdi:Voucher>';

-- Remove the XML declaration and add the required namespaces via a "root" wrapper.
-- Note: You could use STUFF to place root after the declaration and keep it, but this was faster.

SELECT 
    @my_xml = REPLACE ( @my_xml, '<?xml version="1.0" encoding="UTF-8"?>', '' ),
    @my_xml = CONCAT ( '<root xmlns:cfdi="uri" xmlns:tfd="uri">', @my_xml, '</root>' );

-- Return the values for Total and Rfc.

SELECT
    t.c.value('declare namespace cfdi="uri"; (@Total)[1]', 'decimal(18,2)') AS Total,
    t.c.value('declare namespace cfdi="uri"; (cfdi:Issuer/@Rfc)[1]', 'varchar(50)') AS Rfc
FROM ( SELECT CAST ( @my_xml AS xml ) AS xml_data ) AS x
CROSS APPLY x.xml_data.nodes( 'declare namespace cfdi="uri"; //root/cfdi:Voucher' ) t(c);

Returns

+----------+--------------+
|  Total   |     Rfc      |
+----------+--------------+
| 45264.13 | ABC123456AB1 |
+----------+--------------+

Assuming I understand your validation and requirements, you could adjust your stored procedure to accept your XML as a parameter and then do your validation/handling of it accordingly.

Here is an example for reference:

CREATE OR ALTER PROCEDURE [dbo].[sptest] (
    @my_xml varchar(MAX)   
)
AS
BEGIN
    
    DECLARE
        @rfc varchar(50), @qty int, @price decimal(18,2), @total decimal(18,2);

    /*****************************************/
    /*      Fetch values for validation      */
    /*****************************************/

    SELECT
        @rfc = VTB.RFC AS 'RFC',
        @qty = LRC.QTY AS 'QTY',  
        @price = PL.PRICE AS 'Price',
        @total = (PL.PRICE * LRC.QTY) * 1.16 AS 'Total'
    FROM PACKING AS RC  
    INNER JOIN VTABLE AS VTB 
        ON VTB.COLUMN = RC.INVOICE
    INNER JOIN TRANS AS LRC 
        ON (LRC.PACKING = RC.RECID)
    INNER JOIN PURCH AS PL 
        ON (PL.NUMBER =LRC.NUM and PL.PURCH =RC.PURCH)      

    /*****************************************/
    /*     Fetch XML values to validate      */
    /*****************************************/

    DECLARE
        @x_rfc varchar(50), @x_total decimal(18,2);

    -- Remove the XML declaration and add the required namespaces via a "root" wrapper.
    -- Note: You could use STUFF to place root after the declaration and keep it, but this was faster.

    SELECT 
        @my_xml = REPLACE ( @my_xml, '<?xml version="1.0" encoding="UTF-8"?>', '' ),
        @my_xml = CONCAT ( '<root xmlns:cfdi="uri" xmlns:tfd="uri">', @my_xml, '</root>' );

    -- Return the values for Total and Rfc.

    SELECT
        @x_total = t.c.value('declare namespace cfdi="uri"; (@Total)[1]', 'decimal(18,2)'),
        @x_rfc = t.c.value('declare namespace cfdi="uri"; (cfdi:Issuer/@Rfc)[1]', 'varchar(50)')
    FROM ( SELECT CAST ( @my_xml AS xml ) AS xml_data ) AS x
    CROSS APPLY x.xml_data.nodes( 'declare namespace cfdi="uri"; //root/cfdi:Voucher' ) t(c);

    /*****************************************/
    /*        Validate and handle XML        */
    /*****************************************/

    IF @rfc = @x_rfc AND @total = @x_total BEGIN

        -- Validation Success --
        -- Insert your XML to database...

    END ELSE BEGIN

        -- Validation Failure --
        -- Throw and error or whatever you need...

    END

END
GO

I am making a lot of assumptions here (like both your XML and SELECT contain only a single record), but it should get you moving forward.