SQL转XML时如何重命名字段？附示例查询语句

Got it, so you're pulling data from your ECB_ELECTRONICBILL table to generate XML, but you want to swap out those raw column names (like ECB_ID, ECB_PASSWORD_GENERATED) for more clean, readable element names in the output? That's a common need—here are two simple, effective ways to do this with T-SQL:

Method 1: Use FOR XML PATH (Most Flexible) #

This is my go-to approach because it gives you full control over the XML structure, including exact element names. You just need to assign aliases to each column using AS, and those aliases become your XML element names.

Here's how to modify your query:

SELECT TOP (1)
    [ECB_ID] AS 'BillID',               -- Custom XML element name
    [ECB_PASSWORD_GENERATED] AS 'GeneratedPassword',
    [ECB_CONSECUTIVE_NUMBER] AS 'ConsecutiveNumber',
    [ECB_BILL_DATE] AS 'BillDate',
    [TRN_ID] AS 'TransactionID',
    [RCV_ID] AS 'ReceiverID',
    [SLC_ID] AS 'ServiceLocationID',
    [ECB_CREDIT_TIME] AS 'CreditTimestamp',
    [PMN_ID] AS 'PaymentMethodID',
    [IVS_ID] AS 'InvoiceStatusID',
    [RFI_ID] AS 'ReferenceID',
    [NMT_ID] AS 'NotificationMethodID',
    [SCM_ID] AS 'ScheduleID',
    [OTH_ID] AS 'OtherID',
    [ECB_SEND_DATE] AS 'SendDate',
    [MST_ID] AS 'StatusID',
    [ECB_RESPONSE_DATE] AS 'ResponseDate',
    [ECB_PAYDATE] AS 'PaymentDate',
    [MTP_ID] AS 'PaymentTypeID',
    [QRC_ID] AS 'QRCodeID'
FROM [crdx_COREDev1].[dbo].[ECB_ELECTRONICBILL]
FOR XML PATH('ElectronicBill'), ROOT('Bills')

• PATH('ElectronicBill') wraps each row of data in an <ElectronicBill> element.
• ROOT('Bills') adds a top-level <Bills> element around all your rows (remove this if you don't need a root node).
• If you want element names with spaces or special characters, wrap the alias in double quotes, like AS "Bill Date".

Method 2: Use FOR XML AUTO (Simpler for Basic Cases) #

If you prefer a more concise syntax and don't need complex nesting, FOR XML AUTO works too. You'll still use column aliases to define element names, and the ELEMENTS keyword ensures columns are rendered as child elements instead of attributes.

Example:

SELECT TOP (1)
    [ECB_ID] AS 'BillID',
    [ECB_PASSWORD_GENERATED] AS 'GeneratedPassword',
    [ECB_CONSECUTIVE_NUMBER] AS 'ConsecutiveNumber'
    -- Add the rest of your columns with aliases here
FROM [crdx_COREDev1].[dbo].[ECB_ELECTRONICBILL] AS ElectronicBill
FOR XML AUTO, ELEMENTS

• The table alias (ElectronicBill) becomes the parent element name for each row.
• ELEMENTS forces each column alias to be a child element inside that parent.

Quick Tip #

Stick with FOR XML PATH if you ever need to add nested elements, attributes, or custom formatting later—it's far more adaptable than AUTO.

内容的提问来源于stack exchange，提问作者Jason Smith