Hi,
I need to create a SQL query which will create an XML file which will be transmitted using SOAP. However, I am not familiar with XML or SOAP and I need some help. This is my query:
SELECT [fiscalYear.applicationId], noticeNumber, voucherDate, fiscalPeriod.fiscalPeriodCode], ReferenceNumber, [localizedDescriptions], [codingBlock.stringCode], account1, account2, account3, account4, account5, debitAmountDc, creditAmountDc, [commitment.applicationld]
FROM
(
SELECT '02' as noticeNumber, CAST(ROUND(B1.DEBITAMT,2) AS DECIMAL(22,2)) AS debitAmountDc, CAST(ROUND(B1.CRDTAMNT,2) AS DECIMAL(22,2)) AS creditAmountDc, RTRIM(C1.ACTNUMBR_1) + '/' + rtrim(C1.ACTNUMBR_2) + '/' + rtrim(C1.ACTNUMBR_3) + '/' + rtrim(C1.ACTNUMBR_4) + '/' + rtrim(C1.ACTNUMBR_5) AS [codingBlock.stringCode], C1.ACTNUMBR_1 AS account1, C1.ACTNUMBR_2 AS account2, C1.ACTNUMBR_3 AS account3, C1.ACTNUMBR_4 AS account4, C1.ACTNUMBR_5 AS account5, B1.TRXDATE AS voucherDate, B1.JRNENTRY AS ReferenceNumber, B1.REFRENCE AS [localizedDescriptions], B1.JRNENTRY AS [commitment.applicationld], [fiscalPeriod.fiscalPeriodCode] =
CASE
WHEN MONTH(B1.TRXDATE) = '1' THEN 'January'
WHEN MONTH(B1.TRXDATE) = '2' THEN 'February'
WHEN MONTH(B1.TRXDATE) = '3' THEN 'March'
WHEN MONTH(B1.TRXDATE) = '4' THEN 'April'
WHEN MONTH(B1.TRXDATE) = '5' THEN 'May'
WHEN MONTH(B1.TRXDATE) = '6' THEN 'June'
WHEN MONTH(B1.TRXDATE) = '7' THEN 'July'
WHEN MONTH(B1.TRXDATE) = '8' THEN 'August'
WHEN MONTH(B1.TRXDATE) = '9' THEN 'September'
WHEN MONTH(B1.TRXDATE) = '10' THEN 'October'
WHEN MONTH(B1.TRXDATE) = '11' THEN 'November'
WHEN MONTH(B1.TRXDATE) = '12' THEN 'December'
END
,[fiscalYear.applicationId] =
CASE
WHEN MONTH(B1.TRXDATE) BETWEEN '1' AND '3' THEN CONVERT(VARCHAR,YEAR(B1.TRXDATE)-1) + '/' + RIGHT(CONVERT(VARCHAR,YEAR(B1.TRXDATE)),2)
WHEN MONTH(B1.TRXDATE) BETWEEN '4' AND '12' THEN CONVERT(VARCHAR,YEAR(B1.TRXDATE)) + '/' + RIGHT(CONVERT(VARCHAR,YEAR(B1.TRXDATE)+1),2)
END
from GL20000 B1
INNER JOIN GL00105 C1 ON B1.ACTINDX = C1.ACTINDX
where b1.TRXDATE BETWEEN '01/01/2017' AND '12/31/2017')D1
FOR XML path
This is what the xml file should look like:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:srv="http://srv.ws.journalvoucher.gjam.fb.com" xmlns:xsd="http://dto.webservice.gfw.fb.com/xsd" xmlns:xsd1="http://dto.journalvoucher.gjam.fb.com/xsd">
soapenv:Header/
soapenv:Body
srv:saveJournalVoucher
srv:userDto
xsd:applicationUserApplicationIdusername</xsd:applicationUserApplicationId> ------ To be provided by bpms for each combination of agency - system
xsd:passwordJam16!</xsd:password> ------ To be provided by bpms for each combination of agency - system
</srv:userDto>
srv:journalVoucherDto
xsd1:accountingOfficeApplicationId67047</xsd1:accountingOfficeApplicationId> ------ To be provided by bpms. Represents the Ministry of Finance
xsd1:fiscalPeriodFiscalPeriodCodeApril</xsd1:fiscalPeriodFiscalPeriodCode> ------ Represents the fiscal period code. Must be taken from local system but, converted into bpms format which is the same name
------ of the month, with the first letter in Capital.
xsd1:fiscalPeriodFiscalYearApplicationId2015/16</xsd1:fiscalPeriodFiscalYearApplicationId> ------ Represents the fiscal year code. Must be taken from the
------ local system and converted into bpms format which is ------ YYYY/YY. (Initial year YYYY / YY Final year)
xsd1:fiscalYearApplicationId2015/16</xsd1:fiscalYearApplicationId> ------- Redundant value as above.
xsd1:isAutoGeneratedFALSE</xsd1:isAutoGenerated> ------- Constant boolean value. Always FALSE.
xsd1:isReversalFALSE</xsd1:isReversal> ------- Constant boolean value. Always FALSE.
xsd1:isReversedFALSE</xsd1:isReversed> ------- Constant boolean value. Always FALSE.
xsd1:isTemplateFALSE</xsd1:isTemplate> ------- Constant boolean value. Always FALSE.
xsd1:items
xsd1:closeRemainingCommitmentItemTRUE</xsd1:closeRemainingCommitmentItem> ------- Constant boolean value. Always TRUE.
xsd1:closeRemainingObligationItemTRUE</xsd1:closeRemainingObligationItem> ------- Constant boolean value. Always TRUE.
xsd1:codingBlockCoaGroupCoaApplicationIdGOJ 2015 COA</xsd1:codingBlockCoaGroupCoaApplicationId> ------ To be provided by bpms. Hard-coded text.
xsd1:codingBlockCoaGroupCodeGENERAL LEDGER</xsd1:codingBlockCoaGroupCode> ------ To be provided by bpms. Hard-coded text.
xsd1:codingBlockCodingBlockTypeFULL</xsd1:codingBlockCodingBlockType> ------- Constant hard-coded text. Always FULL.
xsd1:codingBlockStringCode01000000/11/1000/0103/00101/0000/0001/210101/01</xsd1:codingBlockStringCode> ------ To be taken from your own local system
------ or database. This value must correspond
------ to the object in the bugdet that is
------ affected in the transaction.
xsd1:isNormalRollupAppliedTRUE</xsd1:isNormalRollupApplied> ------- Constant boolean value. Always TRUE.
xsd1:creditAmountDc500.00</xsd1:creditAmountDc> ------ To be taken from your own database. The amount of the credit account in the JV item.
xsd1:debitAmountDc0.00</xsd1:debitAmountDc> ------ To be taken from your own database. The amount of the debit account in the JV item.
xsd1:isReconciledFALSE</xsd1:isReconciled> ------- Constant boolean value. Always FALSE.
xsd1:originator?</xsd1:originator> ------- This is the number of a commitment that could exist related to the Vourcher
------- It can be null if there is no commitment related to this voucher.
xsd1:subLedgerTypeNOT_APPLICABLE</xsd1:subLedgerType> ------- Constant hard-coded text. Always NOT_APPLICABLE.
xsd1:localizedDescriptions ------ At least one record here. All values must be repeated from the item section.
xsd:applicationLanguageApplicationIdEN</xsd:applicationLanguageApplicationId> ------ It's a constant hard-coded value. Always EN.
xsd:descriptionWHATEVER</xsd:description> ------ If the document has a description for items, this value must be taken from the local system.
------ If not, put here a hard-coded short text describing the JV item.
</xsd1:localizedDescriptions>
</xsd1:items>
xsd1:originWEB_SERVICE</xsd1:origin> ------- Constant hard-coded text. Always WEB_SERVICE.
xsd1:voucherDate2015-01-01T10:00:00</xsd1:voucherDate> ------- Date of the phisical document in bpmsfomat as shown in this example.
xsd1:postingStatusSAVED</xsd1:postingStatus> ------- Constant hard-coded text. Always SAVED.
xsd1:recursOnNextPeriodFALSE</xsd1:recursOnNextPeriod> ------- Constant hard-coded text. Always FALSE.
xsd1:referenceNumbersage12_123</xsd1:referenceNumber> ------- Taken from your own database or system. This must be the number of the phisical
------- document of the JV. Put the username before the number, separated by an ------- underscore, to make it unique and avoid repetitions in the bpms validation.
xsd1:sourceOTHER_EXTERNAL_SOURCE</xsd1:source> ------- Constant hard-coded text. Always OTHER_EXTERNAL_SOURCE.
xsd1:typeREGULAR</xsd1:type> ------- Constant hard-coded text. Always REGULAR.
xsd1:updatePaidAmountInBCFALSE</xsd1:updatePaidAmountInBC> ------- Constant boolean value. Always FALSE.
xsd1:localizedDescriptions ------ At least one record here.
xsd:applicationLanguageApplicationIdEN</xsd:applicationLanguageApplicationId> ------ It's a constant hard-coded value. Always EN.
xsd:descriptionWHATEVER</xsd:description> ------ If the document has a description for the JV, this value must be taken from the local
------ system. If not, put here a hard-coded short text describing the JV document.
</xsd1:localizedDescriptions>
</srv:journalVoucherDto>
</srv:saveJournalVoucher>
</soapenv:Body>
</soapenv:Envelope>
How do I modify my query to get the XML file in this format ?
Thanks