使用SQLXMLBulkLoad库对数据库进行XML转换

Question

我有一个非常复杂的XML，需要使用C#应用程序加载到中。我正在使用.NET标准SQLXMLBulkLoad库来执行此操作。XML如下所示。

<HouseInfo>
    <HouseNumber>1</HouseNumber>
    <HouseLog>
        <RoomInfo>
            <RoomNumber>1</RoomNumber>
            <Timestamp>2017-12-29T12:16:51</Timestamp>
            <Furnitures>
                <Table>
                    <Color>Blue</Color>
                    <Height>23</Height>
                </Table>
            </Furnitures>
            <ToolCounts>
                <Scope>1</Scope>
            </ToolCounts>
        </RoomInfo>

        <RoomInfo>
            <RoomNumber>2</RoomNumber>
            <Timestamp>2017-12-29T15:43:23</Timestamp>
            <Furnitures>
                <Table>
                    <Color>Black</Color>
                    <Height>35.2</Height>
                </Table>
            </Furnitures>
            <ToolCounts>
                <Scope>1</Scope>
            </ToolCounts>
            <Bathroom>
                <Code>1234</Code>
                <Faucets>3></Faucets>
            </Bathroom>
        </RoomInfo>

        <RoomInfo>
            <RoomNumber>2</RoomNumber>
            <Timestamp>2017-12-29T15:45:48</Timestamp>
            <Furnitures>
                <Table>
                    <Color>Red</Color>
                    <Height>98.56</Height>
                </Table>
            </Furnitures>
            <ToolCounts>
                <Scope>1</Scope>
            </ToolCounts>
            <Bathroom>
                <Code>1234</Code>
                <Faucets>2></Faucets>
            </Bathroom>
        </RoomInfo>
    </HouseLog>
</HouseInfo>

我创建了一个XSD (模式)文件，并将XML & XSD传递给lib的执行函数。

在DB中创建一个表以添加所有数据标记，如下所示。

CREATE TABLE HOUSEINFO (House Number INT,
            RoomNumber INT,
            TimeStamp DateTime,
            Color VARCHAR(25),
            Height VARCHAR(25),
            Scope INT,
            Code INT,
            Faucet INT);

这里的目标是将每一行都包含HouseNumber、RoomNumber & TimeStamp作为前三列。该列的其余部分将包含来自RoomInfo中剩余标记的数据。

我自己试过了，但是没有办法确保前三列是上面的列。我认为这是SQLXMLBulkLoad库的一个限制。也就是说，一旦XML & XSD被传递到这个库中，就取决于它如何将数据加载到DB上了，我们没有控制权。这句话对吗？

还有别的方法可以让我做到这一点吗？也就是说，将每个RoomInfo标记中的所有数据放在一行和前三列中，将是RoomNumber & TimeStamp。

这些列是前三列的原因是，编写查询来提取数据很容易。正如您所看到的，HouseNumber对于整个XML是唯一的。RoomNumber &时间戳对于每个RoomInfo都是独一无二的。XML中可以有N个RoomInfo标记。

我对XSD & DB编程非常陌生，在这里真的很吃力。我很感激你的帮助！

Answer 1

我不知道SQLXMLBulkLoad是如何用你的需求来完成你的工作的。这是另一种选择。您可以使用辛乔ETL和SqlBulkCopy将您的xml导入数据库。这是工作样本

static void BulkLoad1()
{
    string connectionstring = /* your db connection string */

    int houseNo = 0;
    using (var xr = new ChoXmlReader("your.xml").WithXPath("/HouseInfo")
        .WithField("HouseNumber", fieldType: typeof(int))
        )
    {
        houseNo = xr.First().HouseNumber;
    }

    using (var xr = new ChoXmlReader("your.xml").WithXPath("/HouseInfo/HouseLog/RoomInfo")
        .WithField("HouseNumber", fieldType: typeof(int), valueConverter: (o) => houseNo)
        .WithField("RoomNumber", fieldType: typeof(int))
        .WithField("Timestamp", fieldType: typeof(DateTime))
        .WithField("Color", xPath: "Furnitures/Table/Color", fieldType: typeof(string))
        .WithField("Height", xPath: "Furnitures/Table/Height", fieldType: typeof(string))
        .WithField("Scope", xPath: "ToolCounts/Scope", fieldType: typeof(int))
        .WithField("Code", xPath: "Bathroom/Code", fieldType: typeof(int))
        .WithField("Faucet", xPath: "Bathroom/Faucets", fieldType: typeof(int))
    )
    {
        using (SqlBulkCopy bcp = new SqlBulkCopy(connectionstring))
        {
            bcp.DestinationTableName = "dbo.HOUSEINFO";
            bcp.EnableStreaming = true;
            bcp.BatchSize = 10000;
            bcp.BulkCopyTimeout = 0;
            bcp.NotifyAfter = 10;
            bcp.SqlRowsCopied += delegate (object sender, SqlRowsCopiedEventArgs e)
            {
                Console.WriteLine(e.RowsCopied.ToString("#,##0") + " rows copied.");
            };
            bcp.WriteToServer(xr.AsDataReader());
        }
    }

希望这能有所帮助。