CODEWITHSUNDEEP
sql-serverxmlxsdschemaxmltable
user4622594
user4622594

Reputation:

Create XSD for defined XML and Table

I would like to build an import interface for a sql-server database which works with XML files. Now I got stuck in creating a XSD File to ensure a correct input-xml.

Let's say I have a table like this:

table:    accounts
colummns: account_id INT NOT NULL
          name VARCHAR(20) NOT NULL
          type CHAR(1) NOT NULL
          desc VARCHAR(100)

and the xml file should look like this:

<accounts>
    <account>
        <account_id>1</account_id>
        <name>account A</name>
        <type>B</type>
    </account>
    <account>
        <account_id>2</account_id>
        <name>account B</name>
        <type>D</type>
        <desc>some text here</desc>
    </account>
</accounts>

It's the first time I am designing something like this and I have no experience with xsd file ... I tried several things like SELECT .. FOR XML AUTO, XMLSCHEMA and XSD.exe but nothing gave me what I wanted.

I want to map the types of the SQL-Server Table in XSD - such like nullable/not nullable and length of strings. Even a range of valid values should be declared (e.g. type can only be A,B,C or D).

Upvotes: 1

Views: 196

Answers (2)

user4622594
user4622594

Reputation:

found a solution with XSD.exe - Just created an XSD an then adopted it, but it's hard work if you're totally new to this topic ;-)

Upvotes: 0

Stepan Novikov
Stepan Novikov

Reputation: 1396

  SELECT * FROM [accounts] FOR XML PATH('account'), ROOT('accounts')

Upvotes: 1

Related Questions