T-SQL查询：无需EXPLICIT和CDATA生成指定嵌套XML结构的方法

Got it, let's break this down. You want to add an outer <customer> wrapper to your XML, move away from the cumbersome EXPLICIT mode, and eliminate the CDATA section entirely. The PATH mode in T-SQL is perfect for this—it's far more intuitive and flexible for building nested XML structures.

I'll cover two scenarios depending on how you want the <P>Name SurName</P> content to behave:

Scenario 1: <P> as a Child XML Element #

If you want the <P> tag to be a proper sub-element of <name> (instead of wrapped in CDATA), use this query:

SELECT
    (
        -- Inner query builds the <customers> block with nested <name> and <P>
        SELECT
            CONCAT('Name', ' ', 'SurName') AS 'P'
        FROM tPA_SysParamSys
        FOR XML PATH('name'), ROOT('customers'), TYPE
    )
FOR XML PATH('customer'), TYPE

How this works: #

• FOR XML PATH('name') generates a <name> element for each row, with a <P> child containing your combined name string.
• ROOT('customers') wraps all <name> elements in a <customers> container.
• The TYPE keyword ensures the inner result stays as XML (not a string), allowing proper nesting inside the outer <customer> element.
• The outer FOR XML PATH('customer') adds the top-level <customer> wrapper you need.

Output XML:

<customer>
  <customers>
    <name>
      <P>Name SurName</P>
    </name>
  </customers>
</customer>

Scenario 2: <P>...</P> as Plain Text (Escaped) #

If you want to keep <P>Name SurName</P> as plain text content inside <name> (without CDATA), T-SQL will automatically escape special characters like < and > for you. Use this query:

SELECT
    (
        SELECT
            CONCAT('<P>', 'Name', ' ', 'SurName', '</P>') AS name
        FROM tPA_SysParamSys
        FOR XML PATH(''), ROOT('customers'), TYPE
    )
FOR XML PATH('customer'), TYPE

How this works: #

• We concatenate the full <P>...</P> string directly as the value of the <name> element.
• PATH('') skips adding an extra wrapper around each row, so we just get the <name> element itself.
• XML PATH mode automatically escapes < to < and > to >, preserving the text without needing CDATA.

Output XML:

<customer>
  <customers>
    <name>&lt;P&gt;Name SurName&lt;/P&gt;</name>
  </customers>
</customer>

Both approaches avoid EXPLICIT mode entirely, skip CDATA, and give you the exact nested structure you requested. Pick the scenario that matches how you want the <P> content to be interpreted!

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