SQLTeam.com | Weblogs | Forums

Creating xml file in SQL using SOAP


#1

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


#2

I don't think your post worked. Most of it is not xml. Try using the </> tags when you post