如何将XML命名空间仅限制在主根节点？附SQL查询示例

Got it, let's fix this so your XML only has namespaces on the root <FE> node instead of scattering them across child nodes. Here's the adjusted approach and code:

Modified SQL Query #

SELECT 
    1 AS [id],
    0 AS [pass],
    -- Subquery to generate the <Others> node without extra namespaces
    (
        SELECT OT.OTH_MESSAGE AS [*]
        FROM [crdx_COREDev1].[dbo].[OTH_OTHERS] AS OT 
        WHERE OT.OTH_ID = E.OTH_ID 
        FOR XML PATH('Others'), TYPE
    ),
    0 AS [CONSECUTIVE]
-- Replace this with your actual main table/query source for E
FROM (SELECT 1 AS OTH_ID) E
-- Define namespaces ONLY on the root FE node here
FOR XML PATH('FE'), 
XMLNAMESPACES(
    DEFAULT 'https://tribunet.hacienda.go.cr/docs/esquemas/2017/v4.2/facturaElectronica',
    'http://www.w3.org/2001/XMLSchema' AS xsd,
    'http://www.w3.org/2001/XMLSchema-instance' AS xsi
);

Key Changes Explained #

1. Removed Global WITH XMLNAMESPACES:
   The original global namespace declaration applied to all FOR XML clauses in the query, including the subquery. This caused child nodes (like <Others>) to inherit or repeat the namespace definitions.

2. Moved Namespaces to the Outer FOR XML:
   By adding XMLNAMESPACES directly after the outer FOR XML PATH('FE'), we restrict the namespace declarations to only the root <FE> node. SQL Server won't propagate these namespaces to nested XML fragments when using the TYPE directive.

3. Subquery Uses TYPE:
   The TYPE keyword in the subquery returns the result as an XML type instead of a string. This tells SQL Server to embed the subquery's XML directly without reprocessing or adding extra namespaces to it.

Resulting XML Structure #

Your output will now look like this (with namespaces only on the root):

<FE xmlns="https://tribunet.hacienda.go.cr/docs/esquemas/2017/v4.2/facturaElectronica" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>1</id>
  <pass>0</pass>
  <Others>Your message content here</Others>
  <CONSECUTIVE>0</CONSECUTIVE>
</FE>

Note: Replace the dummy FROM (SELECT 1 AS OTH_ID) E with your actual main table or query that provides the OTH_ID reference for the subquery.

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