如何将表中所有记录转为XML类型存入另一表指定列（含关联字段）

Hey there! Let's break down your requirement and fix up the approach to get the result you're actually looking for.

Core Requirement Recap #

You want to convert every individual record from TableA into XML format, then store that unique XML in a specific column of TableC. You also need to use reportid and exchange to associate TableA with TableB, and you're asking if an ID-based logic is necessary here.

Should You Use ID Logic? #

Absolutely—if your goal is to have each row in TableC map to a single row from TableA (with its own unique XML data), you need to lean on TableA's unique key (like ID) to generate individual XML fragments.

Your original code creates one big XML blob with all matching records and inserts it into every row of TableC, which means every entry in TableC will have the exact same XML content. That's almost certainly not what you want, so ID-based logic is the right call here.

Step-by-Step Implementation Logic #

1. For each record in TableA (identified by its unique ID), generate a dedicated XML snippet that represents only that row's data.
2. Use reportid and exchange to join TableA with TableB—this can be for filtering records to only those with matches in TableB, or for including TableB's fields in the generated XML.
3. Insert each TableA record's ID, record_no, unique XML, and other required values into the corresponding columns of TableC.

Optimized Example Code #

-- Insert each TableA record's unique XML into TableC
INSERT INTO TableC (ID, record_no, xml_column, placeholder_column, create_date)
SELECT
    A.ID,
    A.record_no,
    -- Generate unique XML for the current TableA row
    (
        SELECT
            A.ID AS [ID],
            A.EventType AS [EventType],
            A.ClientMsgID AS [ClientMsgID],
            A.SessionID AS [SessionID],
            A.Protocol AS [Protocol]
            -- Uncomment below if you need to include fields from TableB
            -- , B.ReportName AS [ReportName]
        FOR XML PATH('row'), TYPE
    ) AS xml_column,
    NULL,
    GETDATE() AS create_date
FROM TableA A
-- Join with TableB to filter records or access its data
JOIN TableB B 
    ON A.ReportID = B.ReportID 
    AND A.[exchange] = B.[exchange];

Key Details About This Code #

• The subquery with FOR XML PATH('row'), TYPE generates a unique XML fragment for each row in TableA, instead of lumping all records into one variable. The TYPE keyword ensures we get an actual XML data type (not a string), which is ideal for storing in your XML column.
• If you need to include data from TableB in the XML, just add those fields to the inner SELECT statement.
• The JOIN with TableB is kept to honor your requirement of using reportid and exchange for association—this filters TableA records to only those that have a matching entry in TableB.

What Was Off With Your Original Code? #

Your original code creates one XML variable (@VAL) that contains all matching TableA records, then inserts that same XML into every row of TableC. This results in every entry in TableC having identical XML content, which doesn't align with storing each TableA record's unique data as XML.

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