I am using SQL Server 2005. Is there any command or GUI tool (e.g. any menu/function from SQL Server management studio) to convert database table into XML schema file (.xsd)?
thanks in advance, George
-
Check this link.... To download an Application to convert a SQL Server database table into XML sites.google.com/site/createxmlfromdbsam– sam2012-09-29 09:03:09 +00:00Commented Sep 29, 2012 at 9:03
Add a comment
|
3 Answers
I've found this. Give it a try
Select CourseID, Name, CoursePrice
FROM CouseMaster.Course Course
FOR XML AUTO, XMLSCHEMA
11 Comments
George2
@Mork0075, 1. CourseID, Name, CoursePrice are the column names? 2. I think CouseMaster.Course is the table name, but why you add an additional Course after CouseMaster.Course Course?
Mork0075
This was only a copy and paste snippet. The select and from are not really important, the FOR XML AUTO, XMLSCHEMA should be.
George2
Cool, I am using "Select CourseID, Name, CoursePriceFROM CouseMaster.Course FOR XML AUTO, XMLSCHEMA" and it works. The last question, the additiona Course after CouseMaster.Course means?
Mork0075
I dont know, perhaps its a typo and should be FROM x AS y
George2
@Mork0075, how to output schema to a file?
|
You can write to file like this:
bcp.exe "select top 0 * from (select 1 as iCol) as t for xml auto, xmlschema" queryout outfile.xsd -T -c
I'm Using the TOP 0 to exclude the xml of the actual query data since you only want the schema. The -c causes it to be plain character data in the output, use -w instead if you want utf-16 (unicode) output.
EDIT - and if you want to change the xml structure, look at PATH with FOR XML.
Comments
Declare @SQL nvarchar(1000) SET @SQL= 'bcp.exe '+ '"select * from yourdbname.yourschema.yourtablename for xml path (''record''), ROOT (''tabel'')"' +' queryout '+ 'c:\yourfilename.xsd' +' -w -r -t -SyourServerName -T'
print @SQL EXEC Master..xp_CmdShell @SQL
Replace allvalues starts with 'your', accordingly
