(SQL Server) Hungary NAV Query Taxpayer

Demonstrates the queryTaxpayer request for the Hungarian NAV Online Invoicing System REST API v2.0.

Note: This example requires Chilkat v9.5.0.83 or greater.

For more information, see https://onlineszamla.nav.gov.hu/api/files/container/download/Online%20Szamla_Interfesz%20specifik%C3%A1ci%C3%B3_EN_v2.0.pdf

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Build the following XML:

    -- <?xml version="1.0" encoding="UTF-8"?>
    -- <QueryTaxpayerRequest xmlns="http://schemas.nav.gov.hu/OSA/2.0/api">
    -- 	<header>
    -- 		<requestId>RID215118906689</requestId>
    -- 		<timestamp>2019-09-11T11:11:08.579Z</timestamp>
    -- 		<requestVersion>2.0</requestVersion>
    -- 		<headerVersion>1.0</headerVersion>
    -- 	</header>
    -- 	<user>
    -- 		<login>lwilsmn0uqdxe6u</login>
    -- 		<passwordHash>2F43840A882CFDB7DB0FEC07D419D030D864B47B6B541DC280EF81B937B7A176E33C052B0D26638CC18A7A2C08D8D311733078A774BF43F6CA57FE8CD74DC28E</passwordHash>
    -- 		<taxNumber>11111111</taxNumber>
    -- 		<requestSignature>C5ADE8A2231C509D2887E6C2C4406CC5F72CA25B070AD3E94FADFA3F91A8A3667AF882DEDC7D67E9086E3D34A95886E929ACD8C924CD1E8357C89BEF43BA9126</requestSignature>
    -- 	</user>
    -- 	<software>
    -- 		<softwareId>123456789123456789</softwareId>
    -- 		<softwareName>string</softwareName>
    -- 		<softwareOperation>LOCAL_SOFTWARE</softwareOperation>
    -- 		<softwareMainVersion>string</softwareMainVersion>
    -- 		<softwareDevName>string</softwareDevName>
    -- 		<softwareDevContact>string</softwareDevContact>
    -- 		<softwareDevCountryCode>HU</softwareDevCountryCode>
    -- 		<softwareDevTaxNumber>string</softwareDevTaxNumber>
    -- 	</software>
    -- 	<taxNumber>22222222</taxNumber>
    -- </QueryTaxpayerRequest>

    DECLARE @success int

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @dtNow int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtNow OUT

    EXEC sp_OAMethod @dtNow, 'SetFromCurrentSystemTime', @success OUT
    EXEC sp_OAMethod @dtNow, 'GetAsTimestamp', @sTmp0 OUT, 0
    PRINT @sTmp0

    -- The hash algorithm for the password is SHA512 (not SHA3-512).
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha512'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'
    DECLARE @myPassword nvarchar(4000)
    SELECT @myPassword = 'my-password'
    DECLARE @passwordHash nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'HashStringENC', @passwordHash OUT, @myPassword

    -- Generate a random request ID like "RID215118906689"
    DECLARE @prng int
    -- Use "Chilkat_9_5_0.Prng" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT

    DECLARE @sbRequestId int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRequestId OUT

    EXEC sp_OAMethod @sbRequestId, 'Append', @success OUT, 'RID'
    EXEC sp_OAMethod @prng, 'RandomString', @sTmp0 OUT, 12, 1, 0, 0
    EXEC sp_OAMethod @sbRequestId, 'Append', @success OUT, @sTmp0

    EXEC sp_OAMethod @sbRequestId, 'GetAsString', @sTmp0 OUT
    PRINT 'generated requestId = ' + @sTmp0

    -- Calculate the requestSignature
    -- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater.  The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
    -- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater.  The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
    -- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater.  The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
    -- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater.  The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
    -- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater.  The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha3-512'
    DECLARE @signatureKey nvarchar(4000)
    SELECT @signatureKey = 'ce-8f5e-215119fa7dd621DLMRHRLH2S'

    DECLARE @sbFinalHashBase int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbFinalHashBase OUT

    -- First append the timestamp because we are going to remove certain chars/parts.
    EXEC sp_OAMethod @dtNow, 'GetAsTimestamp', @sTmp0 OUT, 0
    EXEC sp_OAMethod @sbFinalHashBase, 'Append', @success OUT, @sTmp0
    DECLARE @numReplaced int
    EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, 'Z', ''
    EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, '-', ''
    EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, ':', ''
    EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, 'T', ''

    -- Prepend the requestId and append the signatureKey
    EXEC sp_OAMethod @sbRequestId, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @sbFinalHashBase, 'Prepend', @success OUT, @sTmp0
    EXEC sp_OAMethod @sbFinalHashBase, 'Append', @success OUT, @signatureKey

    DECLARE @requestSignature nvarchar(4000)
    EXEC sp_OAMethod @sbFinalHashBase, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'HashStringENC', @requestSignature OUT, @sTmp0

    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OASetProperty @xml, 'Tag', 'QueryTaxpayerRequest'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns', 'http://schemas.nav.gov.hu/OSA/2.0/api'
    EXEC sp_OAMethod @sbRequestId, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'header|requestId', @sTmp0
    EXEC sp_OAMethod @dtNow, 'GetAsTimestamp', @sTmp0 OUT, 0
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'header|timestamp', @sTmp0
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'header|requestVersion', '2.0'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'header|headerVersion', '1.0'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'user|login', 'lwilsmn0uqdxe6u'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'user|passwordHash', @passwordHash
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'user|taxNumber', '11111111'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'user|requestSignature', @requestSignature
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareId', '123456789123456789'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareName', 'string'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareOperation', 'LOCAL_SOFTWARE'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareMainVersion', 'string'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareDevName', 'string'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareDevContact', 'string'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareDevCountryCode', 'HU'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'software|softwareDevTaxNumber', 'string'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'taxNumber', '22222222'

    -- POST the XML to https://api-test.onlineszamla.nav.gov.hu/invoiceService/v2/queryTaxpayer
    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    EXEC sp_OASetProperty @http, 'Accept', 'application/xml'
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/xml'
    DECLARE @endpoint nvarchar(4000)
    SELECT @endpoint = 'https://api-test.onlineszamla.nav.gov.hu/invoiceService/v2/queryTaxpayer'
    DECLARE @resp int
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'PostXml', @resp OUT, @endpoint, @sTmp0, 'utf-8'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @dtNow
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @sbRequestId
        EXEC @hr = sp_OADestroy @sbFinalHashBase
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @http
        RETURN
      END


    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response status code = ' + @iTmp0

    DECLARE @respXml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @respXml OUT

    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @respXml, 'LoadXml', @success OUT, @sTmp0

    PRINT 'Response body:'
    EXEC sp_OAMethod @respXml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @resp

    -- The result looks like this:

    -- <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    -- <QueryTaxpayerResponse xmlns="http://schemas.nav.gov.hu/OSA/2.0/api" xmlns:ns2="http://schemas.nav.gov.hu/OSA/2.0/data">
    --     <header>
    --         <requestId>RID847153193061</requestId>
    --         <timestamp>2020-03-24T16:00:34Z</timestamp>
    --         <requestVersion>2.0</requestVersion>
    --         <headerVersion>1.0</headerVersion>
    --     </header>
    --     <result>
    --         <funcCode>OK</funcCode>
    --     </result>
    --     <software>
    --         <softwareId>123456789123456789</softwareId>
    --         <softwareName>string</softwareName>
    --         <softwareOperation>LOCAL_SOFTWARE</softwareOperation>
    --         <softwareMainVersion>string</softwareMainVersion>
    --         <softwareDevName>string</softwareDevName>
    --         <softwareDevContact>string</softwareDevContact>
    --         <softwareDevCountryCode>HU</softwareDevCountryCode>
    --         <softwareDevTaxNumber>string</softwareDevTaxNumber>
    --     </software>
    --     <infoDate>1993-01-01T00:00:00.000+01:00</infoDate>
    --     <taxpayerValidity>true</taxpayerValidity>
    --     <taxpayerData>
    --         <taxpayerName>some taxpayer name</taxpayerName>
    --         <taxNumberDetail>
    --             <ns2:taxpayerId>22222222</ns2:taxpayerId>
    --             <ns2:vatCode>2</ns2:vatCode>
    --         </taxNumberDetail>
    --         <taxpayerAddressList>
    --             <taxpayerAddressItem>
    --                 <taxpayerAddressType>HQ</taxpayerAddressType>
    --                 <taxpayerAddress>
    --                     <ns2:countryCode>HU</ns2:countryCode>
    --                     <ns2:postalCode>1121</ns2:postalCode>
    --                     <ns2:city>BUDAPEST 12</ns2:city>
    --                     <ns2:streetName>ABCD</ns2:streetName>
    --                     <ns2:publicPlaceCategory>UTCA</ns2:publicPlaceCategory>
    --                     <ns2:number>20</ns2:number>
    --                 </taxpayerAddress>
    --             </taxpayerAddressItem>
    --         </taxpayerAddressList>
    --     </taxpayerData>
    -- </QueryTaxpayerResponse>

    -- Use this online tool to generate parsing code from sample XML: 
    -- Generate Parsing Code from XML

    DECLARE @QueryTaxpayerResponse_xmlns nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetAttrValue', @QueryTaxpayerResponse_xmlns OUT, 'xmlns'
    DECLARE @QueryTaxpayerResponse_xmlns_ns2 nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetAttrValue', @QueryTaxpayerResponse_xmlns_ns2 OUT, 'xmlns:ns2'
    DECLARE @requestId nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @requestId OUT, 'header|requestId'
    DECLARE @timestamp nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @timestamp OUT, 'header|timestamp'
    DECLARE @requestVersion nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @requestVersion OUT, 'header|requestVersion'
    DECLARE @headerVersion nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @headerVersion OUT, 'header|headerVersion'
    DECLARE @funcCode nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @funcCode OUT, 'result|funcCode'
    DECLARE @softwareId nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareId OUT, 'software|softwareId'
    DECLARE @softwareName nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareName OUT, 'software|softwareName'
    DECLARE @softwareOperation nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareOperation OUT, 'software|softwareOperation'
    DECLARE @softwareMainVersion nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareMainVersion OUT, 'software|softwareMainVersion'
    DECLARE @softwareDevName nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareDevName OUT, 'software|softwareDevName'
    DECLARE @softwareDevContact nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareDevContact OUT, 'software|softwareDevContact'
    DECLARE @softwareDevCountryCode nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareDevCountryCode OUT, 'software|softwareDevCountryCode'
    DECLARE @softwareDevTaxNumber nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @softwareDevTaxNumber OUT, 'software|softwareDevTaxNumber'
    DECLARE @infoDate nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @infoDate OUT, 'infoDate'
    DECLARE @taxpayerValidity nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @taxpayerValidity OUT, 'taxpayerValidity'
    DECLARE @taxpayerName nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @taxpayerName OUT, 'taxpayerData|taxpayerName'
    DECLARE @ns2_taxpayerId int
    EXEC sp_OAMethod @respXml, 'GetChildIntValue', @ns2_taxpayerId OUT, 'taxpayerData|taxNumberDetail|ns2:taxpayerId'
    DECLARE @ns2_vatCode int
    EXEC sp_OAMethod @respXml, 'GetChildIntValue', @ns2_vatCode OUT, 'taxpayerData|taxNumberDetail|ns2:vatCode'
    DECLARE @taxpayerAddressType nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @taxpayerAddressType OUT, 'taxpayerData|taxpayerAddressList|taxpayerAddressItem|taxpayerAddressType'
    DECLARE @ns2_countryCode nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @ns2_countryCode OUT, 'taxpayerData|taxpayerAddressList|taxpayerAddressItem|taxpayerAddress|ns2:countryCode'
    DECLARE @ns2_postalCode int
    EXEC sp_OAMethod @respXml, 'GetChildIntValue', @ns2_postalCode OUT, 'taxpayerData|taxpayerAddressList|taxpayerAddressItem|taxpayerAddress|ns2:postalCode'
    DECLARE @ns2_city nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @ns2_city OUT, 'taxpayerData|taxpayerAddressList|taxpayerAddressItem|taxpayerAddress|ns2:city'
    DECLARE @ns2_streetName nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @ns2_streetName OUT, 'taxpayerData|taxpayerAddressList|taxpayerAddressItem|taxpayerAddress|ns2:streetName'
    DECLARE @ns2_publicPlaceCategory nvarchar(4000)
    EXEC sp_OAMethod @respXml, 'GetChildContent', @ns2_publicPlaceCategory OUT, 'taxpayerData|taxpayerAddressList|taxpayerAddressItem|taxpayerAddress|ns2:publicPlaceCategory'
    DECLARE @ns2_number int
    EXEC sp_OAMethod @respXml, 'GetChildIntValue', @ns2_number OUT, 'taxpayerData|taxpayerAddressList|taxpayerAddressItem|taxpayerAddress|ns2:number'

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @dtNow
    EXEC @hr = sp_OADestroy @prng
    EXEC @hr = sp_OADestroy @sbRequestId
    EXEC @hr = sp_OADestroy @sbFinalHashBase
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @respXml


END
GO