SQL Server
SQL Server
Hungary NAV Query Taxpayer
See more Hungary NAV Invoicing Examples
Demonstrates the queryTaxpayer request for the Hungarian NAV Online Invoicing System REST API v2.0.Chilkat SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- 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 @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @dtNow int
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
EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT
DECLARE @sbRequestId int
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
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha3-512'
DECLARE @signatureKey nvarchar(4000)
SELECT @signatureKey = 'ce-8f5e-215119fa7dd621DLMRHRLH2S'
DECLARE @sbFinalHashBase int
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
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
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OASetProperty @http, 'Accept', 'application/xml'
DECLARE @endpoint nvarchar(4000)
SELECT @endpoint = 'https://api-test.onlineszamla.nav.gov.hu/invoiceService/v2/queryTaxpayer'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
EXEC sp_OAMethod @http, 'HttpStr', @success OUT, 'POST', @endpoint, @sTmp0, 'utf-8', 'application/xml', @resp
IF @success = 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
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response status code = ' + @iTmp0
DECLARE @respXml int
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
-- 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 @resp
EXEC @hr = sp_OADestroy @respXml
END
GO