Generating Templates using VS .NET
In
this article we will see how we can generate XML template documents using VS
.NET powerful IDE. You can also look at the article were we have discussed
"Generating XSD using VS.NET". So here we go with the step
by step :
Step
1:
Open
VS .NET IDE and Add a new Item (CTRL + SHIFT + A).
Step
2:
In
the New Item dialog, in the templates pane, select XML file.
In the Name Field type Authors.xml.
Step
3:
Add
the following code in the document open.
<?xml
version="1.0"
encoding="utf-8"
?>
<Demo
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<
sql:query
>
</
sql:query
>
</Demo>
Step
4:
Now
we need to connect to SQL Server. Press (CTRL + ALT + S) or click Server
Explorer.
Step
5:
Expand
the Server, Access SQL Server, Click on the Pubs database, Views.
Step
6:
Right
click at the Views menu. Click New View. In the Tables Section double click
Authors, Titles and TitleAuthor. And clicks OK.
Step
7:
Select
the columns you want from the corresponding tables. I've selected au_lname,
au_fname, Title, royaltyper for the example.
Step
8:
Copy
the Select statement and close the query builder window.
Step
9:
Paste
the code between the sql:query tag. And save the XML. The final XML would
ultimately look like.
<?xml
version="1.0"
encoding="utf-8"
?>
<Demo
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<
sql:query
>
SELECT authors.au_id, authors.au_lname, authors.au_fname,
dbo.titles.title,
dbo.titleauthor.royaltyper
FROM dbo.authors authors
INNER JOIN dbo.titleauthor
ON
authors.au_id = dbo.titleauthor.au_id_
INNER JOIN dbo.titles
ON
dbo.titleauthor.title_id = dbo.titles.title_id
</
sql:query
>
</Demo>
And our SQLXML template is all ready. We can use this in our code to retrieve
details about authors. We can test the template using the following code block:
Dim
cmd As SqlXmlCommand
cmd
= New SqlXmlCommand("DSN=netdb;initial
catalog=pubs")
cmd.RootTag
= "Demo"
cmd.CommandText
= "../authors.xml"
cmd.CommandType
= SqlXmlCommandType.TemplateFile
Dim
f As FileStream
f
= New FileStream("c:\Authors.xml",
FileMode.Create)
cmd.ExecuteToStream(f)
f.Close()
Hence in this article we have successfully created and used VS .NET
capabilities to create and execute template files and get XML data out from SQL
Server 2000. Indeed VS .NET has revolutionized the way we work and access
datastore.
|