解决SQL生成ERP用XML时UserArea重复及属性中心列顺序问题

I get it, you're trying to generate the exact XML structure your ERP needs using SQL, but hitting two frustrating issues: that "attribute-centric column must not come after a non-attribute-centric sibling" error, and duplicate UserArea tags breaking parsing. Let's walk through fixing both problems step by step.

Why Your Original Query Failed #

1. Attribute Order Violation: When defining XML nodes with attributes, SQL requires that attribute columns (like @name) come before their corresponding element values in your SELECT list. Your original query interspersed attributes and values across multiple columns, which confuses the XML parser.
2. Duplicate UserArea Tags: By treating each NameValue pair as a separate column path, you were forcing SQL to create a new UserArea/Property hierarchy for each pair instead of nesting all properties under a single UserArea.

Corrected SQL Query #

Here's the adjusted query that matches your target XML structure exactly:

SELECT 
    '9.2' AS '@releaseID', -- Add the root attribute
    (
        SELECT 
            'infor' AS 'Process/Tenant',
            o.LNProject AS [ProjectBudget/IDs/ID],
            (
                -- Generate each BudgetDetail node
                SELECT 
                    'E_2_' + o.LNProject + '_' + ol.Label + ' 0001' AS [ID],
                    ol.ERPPartNumber AS [Item/ItemID/ID],
                    -- Combine all Property nodes under a single UserArea
                    (
                        SELECT 
                            -- First Property: tpptc120.pric
                            (SELECT 'tpptc120.pric' AS [NameValue/@name], ISNULL(ol.CostoItem, 0) AS [NameValue] FOR XML PATH('Property'), TYPE),
                            -- Second Property: tpptc120.cdf_pcom
                            (SELECT 'tpptc120.cdf_pcom' AS [NameValue/@name], ISNULL(ol.PrezzoItem, 0) AS [NameValue] FOR XML PATH('Property'), TYPE),
                            -- Third Property: tpptc120.cdf_pcos
                            (SELECT 'tpptc120.cdf_pcos' AS [NameValue/@name], ISNULL(ol.PrezzoLordoItem, 1) AS [NameValue] FOR XML PATH('Property'), TYPE)
                        FOR XML PATH(''), TYPE -- Merge properties without a wrapper node
                    ) AS [UserArea]
                FROM Exp_OrderSubLines AS ol
                WHERE ol.OrderNO = o.OrderNO
                ORDER BY ol.[LineNo]
                FOR XML PATH('BudgetDetail'), TYPE, ELEMENTS
            ) AS ProjectBudget
        FROM Exp_Orders AS o
        WHERE o.TargetOrderNo = 'SOB000014'
        FOR XML PATH('DataArea'), TYPE, ELEMENTS
    )
FOR XML PATH('ProcessProjectBudget'), TYPE;

Key Fixes Explained #

• Root Attribute Handling: The outer query adds the releaseID="9.2" attribute directly to the ProcessProjectBudget root node, matching your target structure.
• Single UserArea Container: We wrap all three Property subqueries in a parent subquery with FOR XML PATH('')—this merges the individual Property nodes into a single block, which we then assign to the [UserArea] column. This ensures all properties live under one UserArea per BudgetDetail.
• Valid Attribute-Element Order: Each Property is generated in its own subquery, where the @name attribute is defined before the NameValue element value. This complies with SQL's XML rules and eliminates the attribute-centric error.
• Clean Path Syntax: Removed unnecessary spaces from column paths (e.g., [ProjectBudget/IDs/ID] instead of [ProjectBudget / IDs / ID]) to avoid unexpected node structure issues.

What the Output Looks Like #

This query will generate XML that matches your target exactly:

• A single ProcessProjectBudget root with releaseID="9.2"
• One DataArea containing your Process and ProjectBudget nodes
• Each BudgetDetail has an ID, Item block, and a single UserArea with three Property nodes (each with a NameValue element and its name attribute)

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