SQLXML 3.0 Application Skills

1. Generate the schema file of xmlbulkload. You can use the following SQL statement.
Declare @ tablename varchar (100), @ result varchar (8000)

-- Table name
Set @ tablename = 'cc'

Set @ result = '<? XML version = "1.0" encoding = "gb2312"?>
<XSD: schema xmlns: XSD = "http://www.w3.org/2001/XMLSchema"
Xmlns: dt = "urn: Schemas-Microsoft-com: datatypes"
Xmlns: SQL = "urn: Schemas-Microsoft-com: mapping-schema">

<XSD: element name = "table" SQL: Relation = "'+ @ tablename +'">
<XSD: complextype>'

Select @ result = @ result +'
<XSD: attribute name = "'+ A. Name +'" '+
+ Case
When B. Name = 'int' then' type = "XSD: int "'
When B. Name = 'datetime' then' type = "XSD: datetime" SQL: ype = "datetime "'
When B. Name = 'image' then' type = "XSD: base64binary "'
Else 'Type = "XSD: string" 'end
+ Case when columnproperty (A. ID, A. Name, 'isidentity ') = 1 then' SQL: datatype = "uniqueidentifier" 'else' end
+ '/>'

From syscolumns
Left join policypes B on A. xtype = B. xusertype
Inner join sysobjects D on A. ID = D. id and D. xtype = 'U' and D. Status> = 0
Where D. Name = @ tablename
Order by A. ID

Set @ result = @ result +'
</XSD: complextype>
</XSD: Element>
</XSD: schema>'

Print @ result

2,

Generating xsds

In our previous article we saw how we can use xsds in conjunction with SQLXML. but how can we generate xsds rather than typing them separately. here are some simple steps to achieve the same using. net IDE.

Step 1:

Open an Visual Studio. NET project, and on the project menu, click Add new item.

Step 2:

In the Add new item dialog, In the templates pane, select XML schema and open.

Step 3:

From the View menu, click Server Explorer (or press CTRL + ALT + S) to open Server Explorer.

Step 4:

Expand servers, select <machine Name>, SQL servers, select <machine Name>, select pubs database and tables.

Step 5:

Drag and Drop the orders table on to the design surface.

Step 6:

We are done in generating the XSD. Now move to the XML tab. You can see an ouput as outlined.

Step 7:

Now we cannot use this XSD generated directly. we have to have a couple of modifications and have to remove some content to get the final version. I 've removed some parts of the code and you can view the final document version.