XML. Get from SQL. Dataset.ReadXML(filePath) Publish

How do I get XML data from an SQL Select statement

SELECT [ID],[Name],[Notes],[Example],[DateInput] FROM [SystemDesign].[dbo].[Connections]
FOR XML PATH(‘Connection’), ROOT(‘Connections’)

The output could be copied into a new XML file in Visual Studio

How do I read XML data into a DataSet

Dim filePath = String.Concat(My.Application.Info.DirectoryPath, “\XMLData\Connections.xml”)
Dim filePathSchema = String.Concat(My.Application.Info.DirectoryPath, “\XMLData\Connections.xsd”)
Using ds As New DataSet
ds.ReadXmlSchema(filePathSchema)
ds.ReadXml(filePath)
Me.dsObjectControl1.Connections.Merge(ds.Tables(0))
End Using

How do I distribute and Publish the MyFile.xml and MyFile.xsd

Make sure that on the properties of both these files that:
“Build Action” = “Content”
“Copy to Output Directory” is “Copy if Newer” or “Copy Always”
On Project > Properties > Publish > Application Files > Find <FileName>.xml file and set Publish Status to “Include”

What other considerations are there?

Dataset.ReadXMLSchema(filepath) normally first
Otherwise Dataset.ReadXML will return a table, but the columns are probably of the wrong datatype
So in the above example, the merge will not work because the strongly type dsObjectControl.Connections uses a proper schema.

For the same reason if you hard-code bind the newly created ds.Tables(0) to a bindingsource, this may not work if the schema is important.

How do I get an xsd file?

There is a tool in Visual Studio on Add new item > Data > XML to Schema

How do I validate my xml file against a xsd file?

Possible by adding . See Toolkit.sln. Also links here:
http://msdn.microsoft.com/en-us/library/ms759142(VS.85).aspx
http://msdn.microsoft.com/en-us/library/ms757051(v=VS.85).aspx

How do I get my new dataset to hold the schema from the xml file

TODO currently only by using the Dataset.ReadXMLSchema(filepath) as listed above. It does not get it right on it’s own.

How do I convert my xsd file into a dataset

Was able to do this, mostly by opening it. Also by adding the Custom Tool.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: