Search code examples
sqlxmlfor-xml

For XML in SQL Server - adding Child elements

I have this following query

enter image description here

Returns this XML File Format

enter image description here

How Can I get it in this format?

enter image description here

Please Help, source Code 

SELECT top 1

    (SELECT top 1
    count(*)AS 'number-of-accounts', 
    sum(br.cnsmr_accnt_bckt_crrnt_bal_amnt) AS 'total-value-of-account-payment',
    '' AS 'date-of-turnover',
    '' AS 'is-conversion-job'
    FROM
    cnsmr as c
    inner join cnsmr_accnt as ca ON c.cnsmr_id = ca.cnsmr_id
    inner join cnsmr_accnt_bckt_bal_rprtng as br on ca.cnsmr_accnt_id = br.cnsmr_accnt_id
     FOR XML PATH('payment-import-header'), TYPE),

    (SELECT  top 1
    br.cnsmr_accnt_bckt_crrnt_bal_amnt AS 'payment-amount',
    '' AS 'bucket-transaction-type',
    '' AS 'tendered-date',
    '' AS 'entered-date',
    '' AS 'payment-location-code',
    '' AS 'payment-memo-code',
    '' AS 'payment-comments',
    '' AS 'external-reference-identifier'

    FROM
    cnsmr as c
    inner join cnsmr_accnt as ca ON c.cnsmr_id = ca.cnsmr_id
    inner join cnsmr_accnt_bckt_bal_rprtng as br on ca.cnsmr_accnt_id = br.cnsmr_accnt_id

    FOR XML PATH('consumer-payment'), ROOT('consumer-payment-import'),TYPE)

FOR XML RAW (''), ROOT ('consumer-payment-import-job'), ELEMENTS XSINIL;
Solution

  • Please tag with the actual RDBMS (product and version). Syntax and pictures point to SQL-Server...

    Please read How to ask a good SQL question and How to create a MCVE

    Without test data I cannot test this, but the following should help...

    SELECT top 1

    (SELECT top 1
    count(*)AS 'number-of-accounts', 
    sum(br.cnsmr_accnt_bckt_crrnt_bal_amnt) AS 'total-value-of-account-payment',
    '' AS 'date-of-turnover',
    '' AS 'is-conversion-job'
    FROM
    cnsmr as c
    inner join cnsmr_accnt as ca ON c.cnsmr_id = ca.cnsmr_id
    inner join cnsmr_accnt_bckt_bal_rprtng as br on ca.cnsmr_accnt_id = br.cnsmr_accnt_id
     FOR XML PATH('payment-import-header'), TYPE),

    (SELECT  top 1
    br.cnsmr_accnt_bckt_crrnt_bal_amnt AS 'payment-amount',
    '' AS 'bucket-transaction-type',
    '' AS 'tendered-date',
    '' AS 'entered-date',
    '' AS 'payment-location-code',
    '' AS 'payment-memo-code',
    '' AS 'payment-comments',
    '' AS 'external-reference-identifier'
    ,(
        SELECT 'SomeValue' AS Something
        FOR XML PATH('consumer-identifier'),TYPE
     )
    FROM
    cnsmr as c
    inner join cnsmr_accnt as ca ON c.cnsmr_id = ca.cnsmr_id
    inner join cnsmr_accnt_bckt_bal_rprtng as br on ca.cnsmr_accnt_id = br.cnsmr_accnt_id

    FOR XML PATH('consumer-payment'), ROOT('consumer-payment-import'),TYPE) AS [consumer-payment-imports]

FOR XML RAW (''), ROOT ('consumer-payment-import-job'), ELEMENTS XSINIL;

    The AS [consumer-payment-imports] will wrap the whole inner node within one more node.

    The second addition is one more sub-select to add your customer-reference into the inner sub-select. If this is not 1:n it might be enough to use 

    'SomeValue' AS [consumer-identifier/Something]

    This will add the value SomeValue within a nested element...