在SQL中遍历XML记录集并导入至Options表的技术求助

Got it, let's tackle this problem—you need to parse that variable-structure XML and insert every tableCell (along with its parent tableRow attributes) into your Options SQL table, right? The key here is handling the unknown number of rows and cells without hardcoding anything. Below are two robust approaches depending on your tech stack:

Approach 1: Using C# (.NET) for Flexibility #

This method is great if you're working in a .NET environment, as it gives you full control over parsing logic and works well with large datasets via bulk insertion.

Step 1: Define XML Serialization Models #

First, create classes that map directly to your XML structure—this lets you easily deserialize the XML into strongly-typed objects:

using System.Collections.Generic;
using System.Xml.Serialization;

[XmlRoot("process")]
public class Process
{
    public Return Return { get; set; }
}

public class Return
{
    public Approved Approved { get; set; }
}

public class Approved
{
    public CallbackTable CallbackTable { get; set; }
}

public class CallbackTable
{
    [XmlElement("tableRow")]
    public List<TableRow> TableRows { get; set; }
}

public class TableRow
{
    [XmlAttribute("max")]
    public int Max { get; set; }

    [XmlAttribute("value")]
    public int Value { get; set; }

    [XmlAttribute("selectedRow")]
    public bool SelectedRow { get; set; }

    [XmlAttribute("maxRow")]
    public double MaxRow { get; set; }

    [XmlElement("tableCell")]
    public List<TableCell> TableCells { get; set; }
}

public class TableCell
{
    [XmlAttribute("term")]
    public int Term { get; set; }

    [XmlAttribute("selectedCell")]
    public bool SelectedCell { get; set; }

    [XmlAttribute("maxCell")]
    public int MaxCell { get; set; }

    [XmlElement("number")]
    public double Number { get; set; }
}

Step 2: Parse XML and Bulk Insert to SQL #

Next, deserialize the XML, collect all data points, and use SqlBulkCopy for efficient, low-roundtrip insertion:

using System.Data;
using System.Data.SqlClient;
using System.IO;

// Load your XML content (replace with your file path or string source)
var xmlContent = File.ReadAllText(@"path/to/your/xml/file.xml");

// Deserialize XML to objects
var serializer = new XmlSerializer(typeof(Process));
Process processData;
using (var reader = new StringReader(xmlContent))
{
    processData = (Process)serializer.Deserialize(reader);
}

// Prepare data for bulk insert
var dataTable = new DataTable();
dataTable.Columns.Add("Max", typeof(int));
dataTable.Columns.Add("Value", typeof(int));
dataTable.Columns.Add("SelectedRow", typeof(bool));
dataTable.Columns.Add("MaxRow", typeof(double));
dataTable.Columns.Add("Term", typeof(int));
dataTable.Columns.Add("SelectedCell", typeof(bool));
dataTable.Columns.Add("MaxCell", typeof(int));
dataTable.Columns.Add("Number", typeof(double));

foreach (var row in processData.Return.Approved.CallbackTable.TableRows)
{
    foreach (var cell in row.TableCells)
    {
        dataTable.Rows.Add(
            row.Max,
            row.Value,
            row.SelectedRow,
            row.MaxRow,
            cell.Term,
            cell.SelectedCell,
            cell.MaxCell,
            cell.Number
        );
    }
}

// Insert into SQL Server
using (var connection = new SqlConnection("Your_SQL_Connection_String"))
{
    connection.Open();
    using (var bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "Options";
        // Map columns (skip if XML/SQL column names match exactly)
        bulkCopy.ColumnMappings.Add("Max", "Max");
        bulkCopy.ColumnMappings.Add("Value", "Value");
        bulkCopy.ColumnMappings.Add("SelectedRow", "SelectedRow");
        bulkCopy.ColumnMappings.Add("MaxRow", "MaxRow");
        bulkCopy.ColumnMappings.Add("Term", "Term");
        bulkCopy.ColumnMappings.Add("SelectedCell", "SelectedCell");
        bulkCopy.ColumnMappings.Add("MaxCell", "MaxCell");
        bulkCopy.ColumnMappings.Add("Number", "Number");

        bulkCopy.WriteToServer(dataTable);
    }
}

Approach 2: Direct T-SQL Parsing (SQL Server) #

If you want to handle everything directly in SQL Server (no application code), use built-in XML functions to split and extract the data:

DECLARE @XmlData XML = '<?xml version="1.0" encoding="utf-8"?>
<process xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <return>
    <approved>
      <callbackTable>
        <tableRow max="100" value="10" selectedRow="true" maxRow="112.0">
          <tableCell term="72" selectedCell="false" maxCell="73">
            <number>21.7</number>
          </tableCell>
          <tableCell term="74" selectedCell="true" maxCell="75">
            <number>21.7</number>
          </tableCell>
        </tableRow>
        <tableRow max="200" value="15" selectedRow="false" maxRow="113.0">
          <tableCell term="76" selectedCell="false" maxCell="77">
            <number>14.5</number>
          </tableCell>
          <tableCell term="78" selectedCell="false" maxCell="79">
            <number>22.5</number>
          </tableCell>
        </tableRow>
        <tableRow max="300" value="20" selectedRow="false" maxRow="114.0">
          <tableCell term="80" selectedCell="false" maxCell="81">
            <number>14.6</number>
          </tableCell>
          <tableCell term="82" selectedCell="false" maxCell="83">
            <number>15.7</number>
          </tableCell>
        </tableRow>
      </callbackTable>
    </approved>
  </return>
</process>';

-- Insert into Options table
INSERT INTO Options (Max, Value, SelectedRow, MaxRow, Term, SelectedCell, MaxCell, Number)
SELECT
    -- Extract tableRow attributes
    TR.value('@max', 'INT') AS Max,
    TR.value('@value', 'INT') AS Value,
    TR.value('@selectedRow', 'BIT') AS SelectedRow,
    TR.value('@maxRow', 'FLOAT') AS MaxRow,
    -- Extract tableCell attributes and child element
    TC.value('@term', 'INT') AS Term,
    TC.value('@selectedCell', 'BIT') AS SelectedCell,
    TC.value('@maxCell', 'INT') AS MaxCell,
    TC.value('(number)[1]', 'FLOAT') AS Number
-- Split tableRow nodes into rows
FROM @XmlData.nodes('/process/return/approved/callbackTable/tableRow') AS T(TR)
-- Join with child tableCell nodes for each row
CROSS APPLY TR.nodes('tableCell') AS C(TC);

How This Works: #

• nodes(): Splits the XML into a rowset of nodes (one row per tableRow, then one row per tableCell).
• CROSS APPLY: Links each tableRow to its child tableCell nodes, creating a flat row for every cell-row combination.
• value(): Extracts attribute/element values and converts them to SQL data types (match these to your Options table's column types!).

Key Notes: #

• Data Type Matching: Ensure the SQL data types in the value() calls match your Options table columns (e.g., BIT for boolean values, FLOAT for decimals).
• SQL Injection Safety: Both methods avoid injection—SqlBulkCopy uses parameterized bulk operations, and T-SQL's XML functions are safe.
• Large Datasets: SqlBulkCopy is more efficient for very large XML files, while T-SQL works well for smaller to medium datasets.

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