423,682 Members | 1,622 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 IT Pros & Developers. It's quick & easy.

Mapping XSD to DB via C#

P: 1
I have a XML file as shown below.

Expand|Select|Wrap|Line Numbers
  1. <HouseInfo>
  2.     <HouseNumber>1</HouseNumber>
  3.     <HouseLog>
  4.         <RoomInfo>
  5.             <RoomNumber>1</RoomNumber>
  6.             <Timestamp>2017-12-29T12:16:51</Timestamp>
  7.             <Furnitures>
  8.                 <Table>
  9.                     <Color>Blue</Color>
  10.                     <Height>23</Height>
  11.                 </Table>
  12.             </Furnitures>
  13.             <ToolCounts>
  14.                 <Scope>1</Scope>
  15.             </ToolCounts>
  16.         </RoomInfo>
  17.  
  18.         <RoomInfo>
  19.             <RoomNumber>2</RoomNumber>
  20.             <Timestamp>2017-12-29T15:43:23</Timestamp>
  21.             <Furnitures>
  22.                 <Table>
  23.                     <Color>Black</Color>
  24.                     <Height>35.2</Height>
  25.                 </Table>
  26.             </Furnitures>
  27.             <ToolCounts>
  28.                 <Scope>1</Scope>
  29.             </ToolCounts>
  30.             <Bathroom>
  31.                 <Code>1234</Code>
  32.                 <Faucets>3></Faucets>
  33.             </Bathroom>
  34.         </RoomInfo>
  35.  
  36.         <RoomInfo>
  37.             <RoomNumber>2</RoomNumber>
  38.             <Timestamp>2017-12-29T15:45:48</Timestamp>
  39.             <Furnitures>
  40.                 <Table>
  41.                     <Color>Red</Color>
  42.                     <Height>98.56</Height>
  43.                 </Table>
  44.             </Furnitures>
  45.             <ToolCounts>
  46.                 <Scope>1</Scope>
  47.             </ToolCounts>
  48.             <Bathroom>
  49.                 <Code>1234</Code>
  50.                 <Faucets>2></Faucets>
  51.             </Bathroom>
  52.         </RoomInfo>
  53.     </HouseLog>
  54. </HouseInfo>
  55.  
And i created a XSD from this XML.

Expand|Select|Wrap|Line Numbers
  1. <?xml version="1.0" encoding="utf-8"?>
  2. <!-- Created with Liquid Technologies Online Tools 1.0 (https://www.liquid-technologies.com) -->
  3. <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  4.   <xs:element name="HouseInfo">
  5.     <xs:complexType>
  6.       <xs:sequence>
  7.         <xs:element name="HouseNumber" type="xs:unsignedByte" />
  8.         <xs:element name="HouseLog">
  9.           <xs:complexType>
  10.             <xs:sequence>
  11.               <xs:element maxOccurs="unbounded" name="RoomInfo">
  12.                 <xs:complexType>
  13.                   <xs:sequence>
  14.                     <xs:element name="RoomNumber" type="xs:unsignedByte" />
  15.                     <xs:element name="Timestamp" type="xs:dateTime" />
  16.                     <xs:element name="Furnitures">
  17.                       <xs:complexType>
  18.                         <xs:sequence>
  19.                           <xs:element name="Table">
  20.                             <xs:complexType>
  21.                               <xs:sequence>
  22.                                 <xs:element name="Color" type="xs:string" />
  23.                                 <xs:element name="Height" type="xs:decimal" />
  24.                               </xs:sequence>
  25.                             </xs:complexType>
  26.                           </xs:element>
  27.                         </xs:sequence>
  28.                       </xs:complexType>
  29.                     </xs:element>
  30.                     <xs:element name="ToolCounts">
  31.                       <xs:complexType>
  32.                         <xs:sequence>
  33.                           <xs:element name="Scope" type="xs:unsignedByte" />
  34.                         </xs:sequence>
  35.                       </xs:complexType>
  36.                     </xs:element>
  37.                     <xs:element minOccurs="0" name="Bathroom">
  38.                       <xs:complexType>
  39.                         <xs:sequence>
  40.                           <xs:element name="Code" type="xs:unsignedShort" />
  41.                           <xs:element name="Faucets" type="xs:string" />
  42.                         </xs:sequence>
  43.                       </xs:complexType>
  44.                     </xs:element>
  45.                   </xs:sequence>
  46.                 </xs:complexType>
  47.               </xs:element>
  48.             </xs:sequence>
  49.           </xs:complexType>
  50.         </xs:element>
  51.       </xs:sequence>
  52.     </xs:complexType>
  53.   </xs:element>
  54. </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.
Jan 13 '18 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.