SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Create the tiers table
CREATE TABLE tiers (
    Code VARCHAR(50),
    nom VARCHAR(50),
    Adresse VARCHAR(50),
    CP VARCHAR(50),
    Localite VARCHAR(50),
    CodePays VARCHAR(50),
    CodeLangue VARCHAR(50),
    Tva VARCHAR(50),
    CodeTypeTiers INT
);

INSERT INTO tiers (Code, nom, Adresse, CP, Localite, CodePays, CodeLangue, Tva, CodeTypeTiers)
VALUES
    ('C1', 'Nom1', 'Street1', 'ZipCode1', 'City1', 'BE', 'NL', '', 1),
    ('C2', 'Nom2', 'Street2', 'ZipCode2', 'City2', '', 'FR', '', 3),
    ('C3', 'Nom3', 'Street3', 'ZipCode3', 'City3', NULL, 'EN', '', 1);
3 rows affected

SELECT DISTINCT t.Code AS Prime
    , t.nom AS Alfa
    , t.Nom AS Name
    , t.Adresse AS Street
    , t.CP AS ZipCode
    , t.Localite AS City
    , CASE t.CodePays WHEN NULL THEN 'BE' WHEN '' THEN 'BE' ELSE t.CodePays END AS Country
    , CASE t.CodeLangue WHEN 'NL' THEN 1 WHEN 'FR' THEN 2 WHEN 'EN' THEN 3 WHEN 'DE' THEN 4 ELSE 1 END AS LANGUAGE
    , 'EUR' AS CurrencyCode
    , CASE t.Tva WHEN '' THEN 0 ELSE 1 END AS VATCode
    , CASE t.Tva WHEN '' THEN 0 ELSE 1 END AS VATStatus
    , t.Tva AS VATNumber
    , CASE t.CodePays WHEN NULL THEN 'BE' WHEN '' THEN 'BE' ELSE t.CodePays END AS CountryVATNumber
    , 0 AS STATUS
FROM tiers t
-- INNER JOIN tiersexport te ON t.code=te.code
WHERE t.CodeTypeTiers IN (1, 3)
ORDER BY t.Code
FOR XML PATH('Customer')
    , ROOT('Customers')


XML_F52E2B61-18A1-11d1-B105-00805F49916B


<Customers><Customer><Prime>C1</Prime><Alfa>Nom1</Alfa><Name>Nom1</Name><Street>Street1</Street><ZipCode>ZipCode1</ZipCode><City>City1</City><Country>BE</Country><LANGUAGE>1</LANGUAGE><CurrencyCode>EUR</CurrencyCode><VATCode>0</VATCode><VATStatus>0</VATStatus><VATNumber></VATNumber><CountryVATNumber>BE</CountryVATNumber><STATUS>0</STATUS></Customer><Customer><Prime>C2</Prime><Alfa>Nom2</Alfa><Name>Nom2</Name><Street>Street2</Street><ZipCode>ZipCode2</ZipCode><City>City2</City><Country>BE</Country><LANGUAGE>2</LANGUAGE><CurrencyCode>EUR</CurrencyCode><VATCode>0</VATCode><VATStatus>0</VATStatus><VATNumber></VATNumber><CountryVATNumber>BE</CountryVATNumber><STATUS>0</STATUS></Customer><Customer><Prime>C3</Prime><Alfa>Nom3</Alfa><Name>Nom3</Name><Street>Street3</Street><ZipCode>ZipCode3</ZipCode><City>City3</City><LANGUAGE>3</LANGUAGE><CurrencyCode>EUR</CurrencyCode><VATCode>0</VATCode><VATStatus>0</VATStatus><VATNumber></VATNumber><STATUS>0</STATUS></Customer></Customers>


DECLARE @xml_header NVARCHAR(MAX);
SET @xml_header = N'<?xml version="1.0" encoding="ISO-8859-1"?>\n<ImportExpMPlus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">\n';

DECLARE @xml_footer NVARCHAR(MAX);
SET @xml_footer = N'\n</ImportExpMPlus>';

DECLARE @xml_data NVARCHAR(MAX);
SET @xml_data = (SELECT DISTINCT t.Code AS Prime
                    , t.nom AS Alfa
                    , t.Nom AS Name
                    , t.Adresse AS Street
                    , t.CP AS ZipCode
                    , t.Localite AS City
                    , CASE t.CodePays WHEN NULL THEN 'BE' WHEN '' THEN 'BE' ELSE t.CodePays END AS Country
                    , CASE t.CodeLangue WHEN 'NL' THEN 1 WHEN 'FR' THEN 2 WHEN 'EN' THEN 3 WHEN 'DE' THEN 4 ELSE 1 END AS LANGUAGE
                    , 'EUR' AS CurrencyCode
                    , CASE t.Tva WHEN '' THEN 0 ELSE 1 END AS VATCode
                    , CASE t.Tva WHEN '' THEN 0 ELSE 1 END AS VATStatus
                    , t.Tva AS VATNumber
                    , CASE t.CodePays WHEN NULL THEN 'BE' WHEN '' THEN 'BE' ELSE t.CodePays END AS CountryVATNumber
                    , 0 AS STATUS
                FROM tiers t
                -- INNER JOIN tiersexport te ON t.code=te.code
                WHERE t.CodeTypeTiers IN (1, 3)
                ORDER BY t.Code
                FOR XML PATH('Customer')
                    , ROOT('Customers')
                )

SELECT @xml_header + @xml_data + @xml_footer


(No column name)


<?xml version="1.0" encoding="ISO-8859-1"?>\n<ImportExpMPlus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">\n<Customers><Customer><Prime>C1</Prime><Alfa>Nom1</Alfa><Name>Nom1</Name><Street>Street1</Street><ZipCode>ZipCode1</ZipCode><City>City1</City><Country>BE</Country><LANGUAGE>1</LANGUAGE><CurrencyCode>EUR</CurrencyCode><VATCode>0</VATCode><VATStatus>0</VATStatus><VATNumber></VATNumber><CountryVATNumber>BE</CountryVATNumber><STATUS>0</STATUS></Customer><Customer><Prime>C2</Prime><Alfa>Nom2</Alfa><Name>Nom2</Name><Street>Street2</Street><ZipCode>ZipCode2</ZipCode><City>City2</City><Country>BE</Country><LANGUAGE>2</LANGUAGE><CurrencyCode>EUR</CurrencyCode><VATCode>0</VATCode><VATStatus>0</VATStatus><VATNumber></VATNumber><CountryVATNumber>BE</CountryVATNumber><STATUS>0</STATUS></Customer><Customer><Prime>C3</Prime><Alfa>Nom3</Alfa><Name>Nom3</Name><Street>Street3</Street><ZipCode>ZipCode3</ZipCode><City>City3</City><LANGUAGE>3</LANGUAGE><CurrencyCode>EUR</CurrencyCode><VATCode>0</VATCode><VATStatus>0</VATStatus><VATNumber></VATNumber><STATUS>0</STATUS></Customer></Customers>\n</ImportExpMPlus>