SQL FOR XML按指定元素属性格式导出XML数据问题咨询

目标XML格式 #

客户要求的单条人员数据XML结构如下：

<Person>
    <Item ExportId="Ethnicity">99</Item>
    <Item ExportId="LastName">SMIFF</Item>
    <Item ExportId="FirstName">Arfur</Item>
    <Hospital>
        <Item ExportId="PrimarySite">C7</Item>
        <Item ExportId="RecordNum">C7</Item>
    </Hospital>
</Person>

原SQL的问题 #

你当前的SQL将属性定义和字段值拆分为独立列，导致XML生成了空的<Item>节点和单独的字段节点，无法将值嵌入到<Item>节点内部，不符合目标格式要求。

修改后的SQL语句 #

假设你的数据表包含对应目标XML值的字段（比如Ethnicity、LastName、FirstName、PrimarySite、RecordNum），可以使用FOR XML PATH精准构造嵌套结构：

SELECT
    -- 外层Person节点下的Item节点
    'Ethnicity' AS 'Item/@ExportId',
    Ethnicity AS 'Item',
    'LastName' AS 'Item/@ExportId',
    LastName AS 'Item',
    'FirstName' AS 'Item/@ExportId',
    FirstName AS 'Item',
    -- 嵌套的Hospital节点及其内部Item
    'PrimarySite' AS 'Hospital/Item/@ExportId',
    PrimarySite AS 'Hospital/Item',
    'RecordNum' AS 'Hospital/Item/@ExportId',
    RecordNum AS 'Hospital/Item'
FROM YOUR_TABLE_NAME
FOR XML PATH('Person'), ELEMENTS XSINIL

关键说明 #

• FOR XML PATH('Person')指定根节点为<Person>，匹配目标格式的外层节点
• 通过'节点路径/@属性名'定义节点属性，'节点路径'定义节点的文本内容，确保属性和值属于同一个<Item>节点
• ELEMENTS XSINIL确保即使字段值为NULL，也会生成对应的节点（如果不需要保留空节点可去掉该参数）

如果你的原始字段名和目标XML的ExportId对应关系不同（比如原SQL中的Race对应Ethnicity），只需调整AS 'Item/@ExportId'后的字符串和对应的字段名即可。

内容的提问来源于stack exchange，提问作者G.Emmerson