CODEWITHSUNDEEP
javasqlxml
Jason Smith
Jason Smith

Reputation: 57

Rename Data sql to xml

I have a table with different data, and I'm creating an xml of those tables, but I would like to change the name of those fields in the xml, for example

this my query

 SELECT TOP (1) [ECB_ID]
     ,[ECB_PASSWORD_GENERATED] 
     ,[ECB_CONSECUTIVE_NUMBER]
     ,[ECB_BILL_DATE]
     ,[TRN_ID]
    ,[RCV_ID]
    ,[SLC_ID]
    ,[ECB_CREDIT_TIME]
    ,[PMN_ID]
    ,[IVS_ID]
    ,[RFI_ID]
    ,[NMT_ID]
    ,[SCM_ID]
    ,[OTH_ID]
    ,[ECB_SEND_DATE]
     ,[MST_ID]
   ,[ECB_RESPONSE_DATE]
    ,[ECB_PAYDATE]
    ,[MTP_ID]
     ,[QRC_ID]
   FROM [crdx_COREDev1].[dbo].[ECB_ELECTRONICBILL]
   FOR XML PATH ('FE')

and this is my output xml

 <FE>
 <ECB_ID>1</ECB_ID>
 <ECB_PASSWORD_GENERATED>0</ECB_PASSWORD_GENERATED>
 <ECB_CONSECUTIVE_NUMBER>0</ECB_CONSECUTIVE_NUMBER>
 <ECB_BILL_DATE>2017-10-18T11:03:48.313</ECB_BILL_DATE>
 <TRN_ID>1</TRN_ID>
 <RCV_ID>48418</RCV_ID>
 <SLC_ID>1</SLC_ID>
 <ECB_CREDIT_TIME>0</ECB_CREDIT_TIME>
 <PMN_ID>1</PMN_ID>
 <IVS_ID>196</IVS_ID>
 <RFI_ID>1</RFI_ID>
 <NMT_ID>2</NMT_ID>
 <SCM_ID>1</SCM_ID>
 <OTH_ID>1</OTH_ID>
 <ECB_SEND_DATE>1900-01-01T00:00:00</ECB_SEND_DATE>
 <MST_ID>-1</MST_ID>
 <ECB_RESPONSE_DATE>1900-01-01T00:00:00</ECB_RESPONSE_DATE>
 <ECB_PAYDATE>2017-10-18T11:03:48.313</ECB_PAYDATE>
 <MTP_ID>1</MTP_ID>
<QRC_ID>1</QRC_ID>
</FE>

and I would like the output to be like this

<FE>
 <id>1</id>
 <pass>0</pass>
 <CONSECUTIVE>0</CONSECUTIVE>
  ....
 </FE>

Upvotes: 0

Views: 29

Answers (1)

Paul Abbott
Paul Abbott

Reputation: 7211

Just alias the fields.

 SELECT TOP (1) [ECB_ID] AS id
   ,[ECB_PASSWORD_GENERATED] AS pass
   ,[ECB_CONSECUTIVE_NUMBER] AS CONSECUTIVE...

Upvotes: 1

Related Questions