There are 8 references cited in this article, which can be found at the bottom of the page. The wikiHow Tech Team also followed the article's instructions and verified that they work.
This article has been viewed 43, times. Learn more Log in Social login does not work in incognito and private browsers. Please log in with your username or email to continue. No account yet? Create an account. Edit this Article. We use cookies to make wikiHow great. By using our site, you agree to our cookie policy.
Cookie Settings. Learn why people trust wikiHow. Download Article Explore this Article Steps. Tips and Warnings. At times, this will get quite detailed, so it might be worth getting a brief introduction into XSD if you aren't at all familiar with it. Here is the short list of requirements I came up with for the file format and the process of translating it:. Schema: To keep the project manageable, I decided not to be a completist about the database schema. Instead, my goal was to fully describe a single, real-world database from a client.
This meant:. Version control: Obviously, any file can be version controlled. I wanted a file format that could be edited by multiple people and merged. I also wanted to be able to see changes clearly in the version history of the file. Editing: As far as possible, I wanted a file format that was structured the same way I think about the database schema, designed with hand editing in mind. So I wanted a file format that would guide me by being consistent and self documenting.
Aside from the obvious requirement of being able to translate the file format to SQL, my main concern was convenience. The process should be simple to run from a shortcut, a batch file, or a build step. It should also be convenient to deploy to a new machine.
As the title of this article suggests, I chose XML for my file format. It satisfies the requirements, and it has more general advantages that are beyond the scope of this article. In particular, with a valid XSD attached, Visual Studio will provide intellisense when editing the file. For the uninitiated, XML files have a schema just like databases, although as XML files are hierarchical in nature, the schema takes on a very different form.
Although one is not required to make a valid XML file, a schema is useful because it constrains what is allowed to be present in the file and identifies the format to any person or code that looks at it. I have included the entire XSD file here for reference, but it is rather long, so you may want to collapse it until you are ready to look at it:.
I won't detail every design decision in the XSD, but in general, I used SQL Server Management Studio as a guide for my hierarchy, basing it around the table with indexes and relationships hanging off. Here is a very simple database schema defined with the new format:.
Hopefully, the format is fairly self-explanatory, if you are familiar with the basic concepts of SQL Server. With a little thought, you can probably define your own database using this format without needing any kind of reference. Notice that in the definition of a column, the type is a nested element:. But to do so, the column type would have to support every parameter that is required to describe any type. For example, int can have an identity specification, but decimal can't. Making the column type so generic would seriously impact its usefulness with intellisense.
To make this approach work, I'd need to be able to express this statement in my schema:. The XML document above consists of a root element, "shiporder", that contains a required attribute called "orderid".
The "shiporder" element contains three different child elements: "orderperson", "shipto" and "item". The "item" element appears twice, and it contains a "title", an optional "note" element, a "quantity", and a "price" element. We start by opening a new file that we will call "shiporder. To create the schema we could simply follow the structure in the XML document and define each element as we find it.
We will start with the standard XML declaration followed by the xs:schema element that defines a schema:. Next, we have to define the "shiporder" element. This element has an attribute and it contains other elements, therefore we consider it as a complex type. The child elements of the "shiporder" element is surrounded by a xs:sequence element that defines an ordered sequence of sub elements:.
Then we have to define the "orderperson" element as a simple type because it does not contain any attributes or other elements.
You can also export the current schema as an XML file, or import a schema from an XML file, which must be an export of schema from the Studio, to replace the current schema.
Make sure the data type in the Type column is correctly defined. Object: a generic Talend data type that allows processing data without regard to its content, for example, a data file not otherwise supported can be processed with a tFileInputRaw component by specifying that it has a data type of Object.
0コメント