Nice-Neat XSD file generation
background.....
Quote:
What Is XSD?
XSD provides the syntax and defines a way in which elements and attributes can be represented in a XML document. It also advocates that the given XML document should be of a specific format and specific data type.
XSD is fully recommended by W3C consortium as a standard for defining an XML Document. To know more about latest information on XSD, please refer the W3C site (
www.w3.org).
Advantages of XSD
So what is the benefit of this XSD Schema?
- XSD Schema is an XML document so there is no real need to learn any new syntax, unlike DTDs.
- XSD Schema supports Inheritance, where one schema can inherit from another schema. This is a great feature because it provides the opportunity for re-usability.
- XSD schema provides the ability to define own data type from the existing data type.
- XSD schema provides the ability to specify data types for both elements and attributes.
SQL Server 2005 Express is not only free it has a working Native Client Provider. In addition to generating xml output, the Provider can generate XSD [which is a pain in the kazoo to write by hand] to validate, re-create, explicate, extricate, exonerate... data... but the FOR XML queries usually contain both the xsd and the records. To avoid this add WHERE 0=1 to your SQL. Place the code below into a workbook and it will generate xsd from a table in Adventureworks. I'm open to suggestions on getting the output indented for readability. Use of the xsd for future xPath queries involves a tweak... but that would be more of a knowledgebase article...
Stan
[vba]
Sub createXSD()
cXML = ActiveWorkbook.Path & "\" & "test_xsd.xml"
cProv2 = "Provider=SQLNCLI.1.1;Integrated Security=SSPI;Persist Security Info=False;"
csrc = "Data Source=.\SQLEXPRESS;Initial File Name="
cData = "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf"
adExecuteStream = 1024
If Dir(cData) = "" Then Exit Sub
If Dir(cXML) <> "" Then Kill (cXML)
cConn = cProv2 & csrc & cData
Set oConn = CreateObject("ADODB.Connection")
oConn.Open cConn
Set OS = CreateObject("ADODB.Stream")
Set oCmd = CreateObject("ADODB.Command")
oCmd.CommandTimeOut = 120
oCmd.ActiveConnection = oConn
cSQL = "SELECT * FROM [HumanResources].[Employee] WHERE 0=1 FOR XML AUTO, XMLSCHEMA"
oCmd.CommandText = cSQL
OS.Open
oCmd.Properties("Output Stream").Value = OS
oCmd.Properties("Output Encoding").Value = "ascii"
oCmd.Execute , , adExecuteStream
OS.Position = 0
Var = Replace(OS.ReadText(), ">", ">" & vbCrLf)
OS.WriteText (Var)
OS.SaveToFile cXML, 2
OS.Close
Set OS = Nothing
oConn.Close
Set oConn = Nothing
If Dir(cXML) <> "" Then MsgBox cXML & " created!"
End Sub
[/vba]
