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

Answer

Solution:

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 and{-code-2} 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" ="45264.13">
  <cfdi:Issuer {-code-2}="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  and {-code-2}.

SELECT
    t.c.value('declare namespace cfdi="uri"; (@)[1]', 'decimal(18,2)') AS ,
    t.c.value('declare namespace cfdi="uri"; (cfdi:Issuer/@{-code-2})[1]', 'varchar(50)') AS {-code-2}
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);
Copy code

Returns

+     

Answer

----+ Copy code

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
Copy code

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.

Answer

+

Answer

----+ | Total | Rfc | +

Answer

+

Answer

----+ | 45264.13 | ABC123456AB1 | +

Answer

+

Answer

----+|||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

Answer

Solution:

Since you want to validate the xml data first with the data stored in database so instead of moving the xml file to a permanent location, I would suggest you to read the file and get the values of required fields which you want to validate. To get the values from xml file, You'll have to use any PHP provided XML library likeDOMDocument,XMLReader,SimpleXML or you can use this XML2Array to convert xml to an associative array to easily get the value

if (isset($_FILES["XmlToUpload"])) {
    // Create a unique filename
    $fileName = $_FILES["XmlToUpload"]["name"];
    $ext = pathinfo($fileName, PATHINFO_EXTENSION);
    $archivo = uniqid($fileName, true);
    $ruta ="XML/";
    
    $xmlData = file_get_contents($_FILES['XmlToUpload']['tmp_name']);
        
    $arr = XML2Array::createArray($xmlData);
    
    //Check whether this array contains the required keys or not i.e. Total & Rfc
    if (! isset($arr['cfdi:Voucher']['@attributes']['Total'])) {
        echo "Error";
        exit();
    }
    $totalAmount = $arr['cfdi:Voucher']['@attributes']['Total'];
    
    if (! isset($arr['cfdi:Voucher']['cfdi:Issuer']['@attributes']['Rfc'])) {
        echo "Error";
        exit();
    }
    $rfc = $arr['cfdi:Voucher']['cfdi:Issuer']['@attributes']['Rfc'];
    
    // Validating $totalAmount & $rfc with the data in database
    // Connect to database (Sql Server)
    $serverName = "serverName\\sqlexpress";
    $connectionInfo = array( "Database"=>"dbName", "UID"=>"userName", "PWD"=>"password");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);
    
    if ($conn === false) {
        die( print_r( sqlsrv_errors(), true));
    }
    
    $sql = "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)   
        where VTB.RFC = ? AND ((PL.PRICE * LRC.QTY) * 1.16) = ?";
    
    $params = array($rfc, $totalAmount);
    $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
    $stmt = sqlsrv_query( $conn, $sql, $params, $options);
    $row_count = sqlsrv_num_rows( $stmt );
    
    if ($row_count === false) {
        echo "Error";
    } else {
        if(move_uploaded_file($_FILES["XmlToUpload"]["tmp_name"],$ruta.$archivo)){
            echo "Successfully uploaded";
        }else{
            echo "Error";
        }
    }  
} else {
    echo "A file was not selected";
}
Copy code

Ask a Question