eng. Yahia Galal eng. Yahia Galal - 25 days ago 12
SQL Question

Convert Database To XML with Specific format

I have database with two tables:

Transaction
and
from_person
.

I need to convert it to specified format like this:

<transaction>
<transaction_number>TRNWEB0147</transaction_number
<transaction_location>felesteen</transaction_location>
<date_transaction>2016-05-25T00:00:00</date_transaction>
<from_funds_code>C</from_funds_code>
<from_person>
<gender>M</gender>
<title>Mr.</title>
<first_name>Mohamed</first_name>
<middle_name>Mohamed</middle_name>
<prefix>AHMED</prefix>
<last_name>yahia</last_name>
<birth_date>1984-11-16T00:00:00</birth_date>
<ssn>28411160225124</ssn>
</from_person>
</transaction>


I try to do this query:

select tr.transactionnumber
,tr.transaction_location
,tr.transaction_description
,tr.date_transaction
,tr.teller
,tr.authorized
,tr.transmode_
,tr.amount_local
,(select fp.from_funds_code
,fp.from_country
from from_person fp
where fp.from_funds_code = tr.t_from_my_client
for xml path(''), elements, type)
from dbo. [ transaction ] tr
for xml path(''), elements, type


but result become in this format:

<transaction_number>TRNWEB0147</transaction_number
<transaction_location>felesteen</transaction_location>
<date_transaction>2016-05-25T00:00:00</date_transaction>
<from_funds_code>C</from_funds_code>
<gender>M</gender>
<title>Mr.</title>
<first_name>Mohamed</first_name>
<middle_name>Mohamed</middle_name>
<prefix>AHMED</prefix>
<last_name>yahia</last_name>
<birth_date>1984-11-16T00:00:00</birth_date>
<ssn>28411160225124</ssn>


I tried many ways but not succeed, please help.

Answer

Please use this query.

SELECT transaction_number,transaction_location,date_transaction,from_funds_code,
(
    SELECT gender,title,first_name,middle_name,prefix,last_name,birth_date,ssn
    FROM from_person FP
    WHERE FP.FROM_FUNDS_CODE  =TR.T_FROM_MY_CLIENT 
    FOR XML PATH('from_person'), ELEMENTS, TYPE 
)
FROM dbo.[TRANSACTION] TR
FOR XML PATH('transaction'), ELEMENTS, TYPE
Comments