Wednesday, 13 April 2016

XML : XML for path query to T-SQL query

I have a pretty complex query that uses the 'XML for path' to return an XML. Now I need this query as a regular T-SQL query, but I have great difficulty converting this. I have searched for a tool or another topic that can convert this or give more information how to easily transform this query to a regular t-sql query. Any help would be greatly appreciated!

  SELECT       UPPER([dataareaid]) AS Company,       accountnum AS Code,      name + ' [' +                          CASE ENTERPRISENUMBER                          WHEN 'BTW BE 0000.000.097'                           THEN  TMMONATIONALREGISTRATIONNUMBER                          ELSE                               CASE countryregionid                                   WHEN 'BE'                                   THEN ENTERPRISENUMBER                                  ELSE countryregionid + VATNUM                              END                      END + ']' AS Name,       'false'    AS PaymentDenied,       PAYMTERMID         AS PaymentTermCode,      currency   AS Currency,      AX_TMM.dbo.IS_ACCOUNT_ACTIVE(dataareaid,accountnum) AS Active,      --recid AS Id,       'email' as OrderFormat,      'efinance@test.be' as OrderEmail,      'nl-NL' as [Language],      CASE ENTERPRISENUMBER          WHEN 'BTW BE 0000.000.097'           THEN  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TMMONATIONALREGISTRATIONNUMBER, '.', ''), ',', ''),'-',''),' ', ''),'BTW', '')          ELSE               CASE countryregionid                   WHEN 'BE'                   THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ENTERPRISENUMBER, '.', ''), ',', ''),'-',''),' ', ''),'BTW', '')                  ELSE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE((countryregionid + VATNUM), '.', ''), ',', ''),'-',''),' ', ''),'BTW', '')              END      END AS [SupplierIdentifiers/SupplierIdentifier/value],      'PartyId' AS [SupplierIdentifiers/SupplierIdentifier/key],      CASE ENTERPRISENUMBER          WHEN 'BTW BE 0000.000.097'           THEN  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TMMONATIONALREGISTRATIONNUMBER, '.', ''), ',', ''),'-',''),' ', ''),'BTW', '')          ELSE               CASE countryregionid                   WHEN 'BE'                   THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ENTERPRISENUMBER, '.', ''), ',', ''),'-',''),' ', ''),'BTW', '')                  ELSE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE((countryregionid + VATNUM), '.', ''), ',', ''),'-',''),' ', ''),'BTW', '')              END      END AS [AdditionalDatas/AdditionalData/value],      'PartyId' AS [AdditionalDatas/AdditionalData/name],      (SELECT           accountnum AS [Bban],           ACCOUNTID AS [Name],           name AS [AdditionalData1],           ENTERPRISENUMBER AS [AdditionalData2],           bankiban AS [Iban],           swiftno AS [Swift],           AX_TMM.dbo.IS_BANKACCOUNT_DEFAULT(dataareaid,vendaccount,accountid) AS IsDefault          --vendtable.recid AS Supplierid      FROM           vendbankaccount      WHERE           vendaccount = vendtable.accountnum           and DATAAREAID = vendtable.DATAAREAID          and (expirydate = '1900-01-01 00:00:00.000' or expirydate > getdate())      FOR xml path('SupplierBankAccount'), TYPE) AS [BankAccounts],           (SELECT (SELECT                  '' AS [ExternalCode],                  'PostalAddress' AS [Type],                   'Maatschappelijke zetel ' + SMARTMDCMASTERACCOUNTTABLE.NAME AS [Name],                  'true' AS [IsDefault],                  maaccount + vendtable.DATAAREAID AS [ExternalIdentifier],                        (SELECT (SELECT                           'STREET_NAME' AS [AddressPartKey],                          street AS [AddressPartText]                          FROM                           SMARTMDCMASTERACCOUNTTABLE                          LEFT OUTER JOIN ADDRESSCOUNTRYREGION                          ON ADDRESSCOUNTRYREGION.ISOCODE = SMARTMDCMASTERACCOUNTTABLE.country                          AND ADDRESSCOUNTRYREGION.DATAAREAID = SMARTMDCMASTERACCOUNTTABLE.DATAAREAID                          WHERE                           maaccount = vendtable.accountnum                           FOR xml path('SupplierAddressPart'), TYPE),                      (SELECT           'POSTAL_ZONE' AS [AddressPartKey],          zipcode AS [AddressPartText]          FROM           SMARTMDCMASTERACCOUNTTABLE          WHERE           maaccount = vendtable.accountnum         FOR xml path('SupplierAddressPart'), TYPE          ),          (SELECT           'CITY_NAME' AS [AddressPartKey],          city AS [AddressPartText]          FROM           SMARTMDCMASTERACCOUNTTABLE          WHERE           maaccount = vendtable.accountnum         FOR xml path('SupplierAddressPart'), TYPE          ),          (SELECT           'COUNTRY' AS [AddressPartKey],          ADDRESSCOUNTRYREGION.NAME AS [AddressPartText]          FROM           SMARTMDCMASTERACCOUNTTABLE      LEFT OUTER JOIN ADDRESSCOUNTRYREGION       ON ADDRESSCOUNTRYREGION.ISOCODE = SMARTMDCMASTERACCOUNTTABLE.country          AND ADDRESSCOUNTRYREGION.DATAAREAID = SMARTMDCMASTERACCOUNTTABLE.DATAAREAID      WHERE           maaccount = vendtable.accountnum       FOR xml path('SupplierAddressPart'), TYPE            )          FOR xml path('AddressParts'), TYPE)        FROM           SMARTMDCMASTERACCOUNTTABLE      LEFT OUTER JOIN ADDRESSCOUNTRYREGION       ON ADDRESSCOUNTRYREGION.ISOCODE = SMARTMDCMASTERACCOUNTTABLE.country          AND ADDRESSCOUNTRYREGION.DATAAREAID = SMARTMDCMASTERACCOUNTTABLE.DATAAREAID      WHERE           maaccount = vendtable.accountnum     FOR xml path('SupplierAddress'), TYPE) FOR xml path('SupplierAddresses'), TYPE)    FROM  vendtable   WHERE DATAAREAID = 'TMM'   FOR xml path('Supplier'), ROOT ('DocumentElement')    

No comments:

Post a Comment