Click here to Skip to main content
13,796,787 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hello there,

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

<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
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 14:09pm
Updated 14-Jan-18 1:23am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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...)
  Permalink  
Comments
Donguy1976 14-Jan-18 0:58am
   
I think the best way might be to start with a smaller XML file.

<houseinfo>
<housenumber>1
<houselog>
<roominfo>
<roomnumber>1
<timestamp>2017-12-29T12:16:51




So for the above XML, can you please help me write an XSD and also a DB script that will generate the necessary table(s) in a DB? Thanks!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web05 | 2.8.181207.3 | Last Updated 14 Jan 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100