Convert Database To XML with Specific format

Question

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.

Have you tried setting for xml path('from_person') in your subquery? — TT.
– TT., Commented Nov 7, 2016 at 13:03
"A Subquery or Inner query or Nested query is a query within another SQL query" (quoted from tutorialspoint.com - SQL - Sub Queries). You have only one. In that subquery you use FOR XML PATH(''). Try FOR XML PATH('from_person'). — TT.
– TT., Commented Nov 7, 2016 at 13:12
i know what mean subquery but it's Done Excellent by adding my missed line that you added — eng. Yahia Galal
– eng. Yahia Galal, Commented Nov 7, 2016 at 13:19

Husen · Accepted Answer · 2016-11-07 13:17:24Z

1

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

answered Nov 7, 2016 at 13:17

Husen

1,58510 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert Database To XML with Specific format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related