通过C＃进行XSD到DB映射 [英] XSD to DB mapping via C#

问题描述

你好，



我有一个XML文件，如下所示。

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>

我创建了一个XML Schema文件，如下所示。

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>

创建XSD文件后，我使用了 XSD2DB.exe 在MS SQL Server中创建所有表。



我有一个C＃应用程序，并且 SQLXMLBulkLoad 库被用于将XML内容加载到DB。我将XSD和XML作为参数传递给Execute函数。现在我收到以下错误。

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"

添加到 HouseLog 以及其他一些标签上面的错误，但提出了另一个错误，说它无法找到DB中的特定列，即使它们存在。



所以我想，问题是 - 我需要在XSD中正确定义关系，我不知道如何做到这一点。希望有人可以提出一些解决方案。



请给我一些示例，因为这个架构对数据库对话很新，并且已经用完了想法。在此先感谢。

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.

推荐答案

我不确定你的实际问题是什么，因为XML / XSD样本足够长，可以监督微小问题 - 但你应该：



1>确保您的错误消息包含行号和列，以便更好地查看问题所在的位置。这方面的一个示例是使用DataContractSerializer，但错误对象在其他Xml解析器中应该类似：



请参阅'

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

'部分：

在C＃/ VB.Net中读取和编写XML [ ^ ]





2 - ;如果你不能使用这种更多'细节方法'或者你仍然找不到问题，你也可以组成只包含部分数据的XML / XSD版本（例如：只有HouseInfo，HouseNumber，Houselog），而不是Houselog下面的所有内容。



如果这不起作用，请尝试离开Houselog并查看它是否现在有效，如果有效，请添加更多详细信息以查看它是否现在中断。



这种一步一步的方法应该引导你解决问题发生的情况，这样至少会知道问题所在。



3>此外，它对于查看一些XSD参考或教程显然很有用：

XML Schema Reference [ ^ ]



XML Schemas（XSD）参考 [ ^ ]




我想知道你是否必须指定HouseLog可以多次出现< pre> maxOccurs =unbounded name =HouseLog< / pre> 但我不是很确定（这只是一种感觉......）

' 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...)

我很想帮助你更多，但我不认为我可以做到这一点，因为时间限制，我也没有数据，数据库等...但你还可以做的是加载XML和XSD在Vi sual Studio（文件打开）然后VS可能会强调XML部分，它对XSD不合适......



在VS中编辑XSD是一块蛋糕，因为你得到了这些漂亮的弹出式建议，向你展示了你可能在XSD中缺少的一些选项，希望有帮助......

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

这篇关于通过C＃进行XSD到DB映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！