正在SQL Server中读取XML动态数据

本文关键字：XML 动态 数据 读取 SQL Server 正在 | 更新日期: 2023-09-27 18:00:41

我在一个包含数据集合的表中有一个XML列。每个记录可能包含不同的收集类型，如有时客户数据，有时发票数据等。

我怎样才能阅读那个单元格&将其转换为表，以便将其绑定到数据网格，因此集合有时看起来像

<ArrayOfReceiptTransfer_Receipt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ReceiptTransfer_Receipt>
    <ReceiptTransfer_Receipt_ID>77491</ReceiptTransfer_Receipt_ID>
    <ReceiptTransferID>17839</ReceiptTransferID>
    <ReceiptID>74080</ReceiptID>
    <Amount>500.00</Amount>
  </ReceiptTransfer_Receipt>
</ArrayOfReceiptTransfer_Receipt>

有时看起来像

<ArrayOfInvoiceBudgetItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <InvoiceBudgetItem>
    <InvoiceID>21978</InvoiceID>
    <BudgetItemID>1473</BudgetItemID>
    <Amount>12</Amount>
  </InvoiceBudgetItem>
  <InvoiceBudgetItem>
    <InvoiceID>21978</InvoiceID>
    <BudgetItemID>1475</BudgetItemID>
    <Amount>11</Amount>
  </InvoiceBudgetItem>
</ArrayOfInvoiceBudgetItem>

正在SQL Server中读取XML动态数据

您已经有了一个根标记，所以这也可以使用

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            string input =
            "<ArrayOfReceiptTransfer_Receipt xmlns:xsi='"http://www.w3.org/2001/XMLSchema-instance'" xmlns:xsd='"http://www.w3.org/2001/XMLSchema'">" + 
              "<ReceiptTransfer_Receipt>" +
                "<ReceiptTransfer_Receipt_ID>77491</ReceiptTransfer_Receipt_ID>" +
                "<ReceiptTransferID>17839</ReceiptTransferID>" +
                "<ReceiptID>74080</ReceiptID>" +
                "<Amount>500.00</Amount>" +
              "</ReceiptTransfer_Receipt>" +
            "</ArrayOfReceiptTransfer_Receipt>";
            string xml = string.Format("<?xml version='"1.0'" encoding='"utf-8'"?>{0}", input);
            StringReader reader = new StringReader(xml);
            DataSet ds = new DataSet();
            ds.ReadXml(reader);
            dataGridView1.DataSource = ds.Tables[0];
        }
    }
}
​

试试这个

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            string input =
            "<ArrayOfReceiptTransfer_Receipt xmlns:xsi='"http://www.w3.org/2001/XMLSchema-instance'" xmlns:xsd='"http://www.w3.org/2001/XMLSchema'">" + 
              "<ReceiptTransfer_Receipt>" +
                "<ReceiptTransfer_Receipt_ID>77491</ReceiptTransfer_Receipt_ID>" +
                "<ReceiptTransferID>17839</ReceiptTransferID>" +
                "<ReceiptID>74080</ReceiptID>" +
                "<Amount>500.00</Amount>" +
              "</ReceiptTransfer_Receipt>" +
            "</ArrayOfReceiptTransfer_Receipt>";
            string xml = string.Format("<?xml version='"1.0'" encoding='"utf-8'"?><root>{0}</root>", input);
            StringReader reader = new StringReader(xml);
            DataSet ds = new DataSet();
            ds.ReadXml(reader);
            dataGridView1.DataSource = ds.Tables[1];
        }
    }
}
​
相关文章：