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!

Cinchoo · Accepted Answer · 2018-01-16 14:09:31Z

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.

edited Jan 16, 2018 at 14:09

answered Jan 16, 2018 at 3:52

Cinchoo

6,3562 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

donguy76 Over a year ago

i'm using VS 2010 and installed ChoETL via package manager. But everytime i do a Rebuild, it complains about missing namespace. If i try to re-install then package manager says that it's already installed. The error am getting is - Error 101 The type or namespace name 'ChoETL' could not be found (are you missing a using directive or an assembly reference?)

Cinchoo Over a year ago

it requires .net 4.5. Do u have them?

donguy76 Over a year ago

If i go to Help in VS 2010, the ABOUT box says "Microsoft .NET Framework 4.6.01590"

donguy76 Over a year ago

It's kind of crazy. I uninstalled and reinstalled it. And then immediately VS is able to recognize the ChoETL namespace. But as soon as i try to run the project or build it, the missing namespace error comes up.

Cinchoo Over a year ago

Check and see target framework is set for your project. use this link for more info msdn.microsoft.com/en-us/library/bb398202.aspx

|

Collectives™ on Stack Overflow

XML to DB using SQLXMLBulkLoad lib

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related