1、生成XmlBulkLoad的Schema文件，可以用如下SQL。
declare @tableName varchar(100), @result varchar(8000)
--表名称
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:datatype="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 a
left join systypes 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 VS .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。
