XML to DB using SQLXMLBulkLoad lib

Question

I have a pretty complicated XML that i need to load into MS SQL Server DB using a C# application. Am using the .NET standard SQLXMLBulkLoad library for doing this. The XML is given below.

<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>

I created a XSD (schema) file and passed both XML & XSD to lib's Execute function.

Created a single table in DB to add all data tags as shown below.

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

The goal here is to have every row contain HouseNumber, RoomNumber & TimeStamp as the 1st three columns. The rest of the column will contains data from the remaining tags inside RoomInfo.

I tried it myself, but couldn't find a way to make sure that 1st three columns are the above ones. I think this is a limitation with SQLXMLBulkLoad library. i.e., once the XML & XSD is passed into this lib, it's up to it's discretion on how to load the data to DB and we have no control. Is that statement correct?

Is there another approach by which i can accomplish this? i.e., to have all the data inside each RoomInfo tag in one row and the 1st three columns will be HouseNumber, RoomNumber & TimeStamp?

The reason why these are the 1st three columns is because then it will be easy to write a query to extract the data. As you see HouseNumber is unique to the whole XML. RoomNumber & Timestamp are unique to each RoomInfo. There can be N number of RoomInfo tags in a XML.

Am very new to XSD & DB programming and really struggling here. I appreciate your help!

Answer 1

I dont know how the SQLXMLBulkLoad does the job for you with your requirement. Here is the alternative. You can use Cinchoo ETL along with SqlBulkCopy to import your xml to database. Here is the working sample

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());
        }
    }

Hope this helps.