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