XSD to DB mapping via C#

Question

0.00/5 (No votes)

See more:

Hello there,

I have a XML file as shown below.

HTML

<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 an XML Schema file like shown below.

HTML

<pre lang="HTML">

<?xml version="1.0" encoding="utf-8"?>
<!-- Created with Liquid Technologies Online Tools 1.0 (https://www.liquid-technologies.com) -->
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="HouseInfo">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="HouseNumber" type="xs:unsignedByte" />
        <xs:element name="HouseLog">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="RoomInfo">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="RoomNumber" type="xs:unsignedByte" />
                    <xs:element name="Timestamp" type="xs:dateTime" />
                    <xs:element name="Furnitures">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element name="Table">
                            <xs:complexType>
                              <xs:sequence>
                                <xs:element name="Color" type="xs:string" />
                                <xs:element name="Height" type="xs:decimal" />
                              </xs:sequence>
                            </xs:complexType>
                          </xs:element>
                        </xs:sequence>
                      </xs:complexType>
                    </xs:element>
                    <xs:element name="ToolCounts">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element name="Scope" type="xs:unsignedByte" />
                        </xs:sequence>
                      </xs:complexType>
                    </xs:element>
                    <xs:element minOccurs="0" name="Bathroom">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element name="Code" type="xs:unsignedShort" />
                          <xs:element name="Faucets" type="xs:string" />
                        </xs:sequence>
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

After creating the XSD file i used the XSD2DB.exe to create all the tables in the MS SQL Server.

I have a C# application and in that SQLXMLBulkLoad library is being used to load the XML content to DB. I pass the XSD and XML as parameters to the Execute function. Now am getting below error.

relationship expected on 'HouseLog'.

What I have tried:

I tried adding

HTML

sql:is-constant="1"

to HouseLog and some other tags and that removes the above error, but brings up another error saying that it cannot find specific columns in DB even though they exist.

So i guess, the problem is - i need to define relationships properly in the XSD and i have no idea how to do it. Hope someone can suggest some solutions.

Please give me some samples as am quite new to this schema to DB conversation and have ran out of ideas. Thanks in advance.

Posted 12-Jan-18 13:09pm

Donguy1976

Updated 14-Jan-18 0:23am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dirk Bahle · Answer 1 · 2018-01-12T22:47:00

I am not sure what you actual problem is because the XML/XSD samples are long enough to oversee tiny problems - but you should:

1> Make sure that your error message contains a line number and column to see better where it locates the problem. An example for that is here using the DataContractSerializer but the error object should be similar in other Xml parsers:

See text and project in the '

Making Things Bullet Proof

' section:
Reading and Writing XML in C#/VB.Net[^]

2> If you cannot use this more 'details approach' or you still cannot find the problem you can also make up XML/XSD versions that ctonain only part of the data (eg.: only HouseInfo, HouseNumber, Houselog) leaving out everything else below Houselog.

If that does not work try leaving out Houselog and see if it works now, if it works, add more detail to see if it breaks now.

This step by step approach should lead you over the situation where you problem occurs so will at least know what the problem is.

3> In addition its obviously useful to look at some XSD references or tutorials:
XML Schema Reference[^]

XML Schemas (XSD) Reference[^]

I am wondering if you have to specify that HouseLog can occur more than once <pre>maxOccurs="unbounded" name="HouseLog"</pre> but I am not comepletely sure (its just a feeling...)

Dirk Bahle · Answer 2 · 2018-01-14T00:23:00

Hi, I'd love to help you more but I don't think I could do it because of time constrains and I also don't have the data, the database etc... but what you can also do is to load the XML and the XSD in Visual Studio (File Open) and then VS may underline the XML part where it things that it is not OK against the XSD...

Editing the XSD in VS is a piece of cake since you get these nice pop-up suggestions showing you some options that you might be missing in the XSD, hope that helps...

XSD to DB mapping via C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0