Nice-Neat XSD file generation

[stanl]

background.....

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]

[Oorang]

Have you considered putting together an KB entry? It would make a nice addition.

[stanl]

Not so sure there is any interest. I have put together a tutorial which will be posted on the AutoIntern site, to collaborate with their C++ DOM Parser (loaded as an extender dll), probably could be modified for VBA (~90% code compatibility with Winbatch code). Stan

[Oorang]

There would appear to be some

http://www.vbaexpress.com/forum/showthread.php?t=20113

Not directly connected, I admit, but I think the interest is there. I think the interest is growing with time too.

[stanl]

but I think the interest is there. I think the interest is growing with time too.

Hopefully. Between XSD and directly processing Office 2007 Excel XML data w/out loading Excel would seem to indicate an alternative method to process data.