topic

XML+MSSQL+ASP.NET. A part 1.

Before a spelling of this clause{article}, I was overcome with serious doubts for two reasons. The first is an immensity of the subject, the second is an audience to which the given information can be useful. For professionals to state this material ridiculously, and for "again arrived" - it is necessary to grasp immense within the framework of our genre too much. In one clause{article} of hundred books you will not place.

But, recognizing that our site has a direction on popularization of those or other technologies (decisions) and all materials are based on our own experience, we came to opinion that it is necessary to state those results of job with the XML-given which at us turned out in the best way. If also there are discrepancies in a statement of a material concerning serviceability of examples to doubt it is not necessary. All remarks are welcomed and accepted on email qstart@narod.ru

In itself XML it is primitive. An initial line <? xml version = " 1.0"?>, a root element (it is not necessary <root>, any name), with an obligatory closing element, and as the enclosed elements (tegi) with attributes and values. Any structure, any enclosure. And all. The shortest description of language is stacked in three lines. But on this elementary markup language of documents complex{difficult} technologies are wound many. Languages XPath (search and selection of sites) and XSLT (transformation of documents), Web-services (SOAP), MSSQL (a data storage in a XML-format in MSSQL2005, performance of results of searches in XML - in MSSQL2000) and many other things.

So, a sheaf ASP.NET, MSSQL2000 and XML. To develop this subject of us the problem{task} for the user in a browser has forced to deduce{remove} a database from more than 6 thousand lines. In classical ASP, in a cycle, the conclusion of the table of such size borrowed{occupied} about 5-7 minutes. This time did not suit the user in any way. And here appeared, that the conclusion of the data from MSSQL in XML a kind borrows{occupies} few seconds. It was necessary to transform a XML-stream to the table so it was necessary to master shablonizaciju on basis XSLT only. It is background of a question which has been solved by means classical ASP.

ASP.NET gives much richer toolkit for such decision of problems{tasks}, therefore if and to master software product, more progressive.

For use of all functionality it is necessary on the server (at us SQL and IIS are established by one machine) to install SqlXml 3.0 Service Pack 3 (SP3). To download it{him} it is possible under the link http: // www.microsoft.com/downloads/details.aspx? familyid=51D4A154-8E23-47D2-A033-764259CFB53B*displaylang=en. On this page as it is briefly described for what the given product (in English) is intended.

At once I want to make a reservation, that results of all examples can be received and without installation SqlXml, on standard delivery ASP.NET, but as I have said above if and to apply high technologies forward, on SqlXml.

After installation of this product on a computer in Program Files the section " SQLXML 3.0" in which there is a CHM-file with the documentation and examples of use will appear.

Let's write the first program (a file with expansion aspx)

<% Page Language = "VB" %>

<% import Namespace = " Microsoft. Data. SqlXML " %>

Sub Page_Load (sender As Object, e As EventArgs)

Dim cmd as SqlXmlCommand

cmd = New SqlXmlCommand (" Provider=SQLOLEDB; server = (local); database=pubs; uid=sa; password=sa ")

cmd. CommandText = " SELECT * FROM Titles FOR XML AUTO "

Response. ContentType = "text/xml"

cmd. RootTag = "root"

Response. Clear ()

cmd. ExecuteToStream (Response. OutputStream)

End SUb

</script>

File we shall save as primer.aspx (in the catalogue wwwroot) and in a line of the address a browser we shall type{collect} http://localhost/primer.aspx

If it is started IIS, NET Framework it is established, MSSQL functions, we shall receive the following picture (results are reduced)

<? xml version = " 1.0" encoding = "utf-8"?>

<root>

</root>

The given kind of the document turns out on mode FOR XML AUTO. Names of lines are identical with the name of the table, values of fields of recordings are submitted as attributes.

That attributes to transform into the enclosed elements we shall use option ELEMENTS. Search

cmd. CommandText = " SELECT * FROM Titles FOR XML AUTO, ELEMENTS "

Transforms attributes <titles> into independent elements. Results of performance of search are reduced.

<? xml version = " 1.0" encoding = "utf-8"?>

<root>

<title_id> BU1032 </title_id>

<title> The Busy Executive's Database Guide </title>

<type> business </type>

<pub_id> 1389 </pub_id>

<ytd_sales> 4095 </ytd_sales>

<notes> An overview of available database systems with emphasis on common business applications. Illustrated. </notes>

</Titles>

<title_id> BU1111 </title_id>

<title> Cooking with Computers: Surreptitious Balance Sheets </title>

<type> business </type>

<pub_id> 1389 </pub_id>

<ytd_sales> 3876 </ytd_sales>

<notes> Helpful hints on how to use your electronic gt; resources to the best advantage. </notes>

</Titles>

<title_id> BU2075 </title_id>

<title> You Can Combat Computer Stress! </title>

<type> business </type>

<pub_id> 0736 </pub_id>

<ytd_sales> 18722 </ytd_sales>

<notes> The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations. </notes>

</Titles>

</root>

But the most interesting opportunity is mode SELECT'? - FOR XML EXPLICIT. This design allows to form the XML-given in that structure (hierarchy) in which it is necessary for you. The given mode the full control over structure of the XML-document, but a payment for it - allows to establish more complex{difficult} use.

The structure of the target document is described in terms of the universal table. The universal table is a performance of a resulting data set with special headings stolbcov, which speak the SQL-server how to create the document. The instruction necessarily begins with SELECT 1 AS Tag, NULL AS Parent. Further fields of the table and their names on special format Element are listed{transferred}! Tag! Attribute! Directive

Further I shall not go deep into a jungle, and I shall try to illustrate on examples. As to explain the theory of use of this design it is better than Ken Henderson I cannot. Coordinates of the book of this author (" the Professional management{manual} on SQL Server: ") I shall result at the end of clause{article}.

cmd. CommandText = " select 1 AS Tag, null as Parent, pub_id AS [the Book! 1! The publisher], title AS [the Book! 1! The name], price AS [the Book! 1! The price], ytd_sales AS [the Book! 1! Sales] FROM titles FOR XML EXPLICIT "

We receive

<? xml version = " 1.0" encoding = "utf-8"?>

<root>

</root>

While anything especial, not considering names of elements and attributes in Russian.

If we shall want to transform attributes into independent elements transformation is organized very simply. To a name of a column it is added Directive - Element, i.e. [Row! 1! The name! Element]

cmd. CommandText = " select 1 AS Tag, null as Parent, pub_id AS [the Book! 1! The publisher], title AS [the Book! 1! The name! Element], price AS [the Book! 1! The price! Element], ytd_sales AS [the Book! 1! Sales! Element] FROM titles FOR XML EXPLICIT "

<? xml version = " 1.0" encoding = "utf-8"?>

<root>

<Name> The Busy Executive's Database Guide </name>

</book>

<Name> Cooking with Computers: Surreptitious Balance Sheets </name>

</book>

<Name> You Can Combat Computer Stress! </name>

</book>

<Name> Straight Talk About Computers </name>

</book>

<Name> Silicon Valley Gastronomic Treats </name>

</book>

</root>

Let's complicate a problem{task}. For example, we want to unit recordings on the identifier of the publisher (pub_id), i.e. to collect all editions for everyone pub_id and to deduce{remove} them as subordinated to the identifier. In such search it is necessary to unit the table with itself, on a field pub_id, having established thus hierarchical interrelation between elements.

cmd. CommandText = " select 1 AS Tag, null as Parent, pub_id AS [the Publisher! 1! Number{room}], NULL AS [the Book! 2! The name! Element], NULL AS [the Book! 2! The price! Element], NULL AS [the Book! 2! Sales! Element] from titles UNION SELECT 2 AS Tag, 1 AS Parent, pub_id, title, price, ytd_sales FROM titles ORDER BY [the Publisher! 1! Number{room}] FOR XML EXPLICIT "

<? xml version = " 1.0" encoding = "utf-8"?>

<root>

<Book>

<Name> Emotional Security: A New Algorithm </name>

</book>

<Book>

<Name> Is Anger the Enemy? </name>

</book>

<Book>

<Name> Life Without Fear </name>

</book>

<Book>

<Name> Prolonged Data Deprivation: Four Case Studies </name>

</book>

<Book>

<Name> You Can Combat Computer Stress! </name>

</book>

</publisher>

<Book>

<Name> Computer Phobic AND Non-Phobic Individuals: Behavior Variations </name>

</book>

<Book>

<Name> Fifty Years in Buckingham Palace Kitchens </name>

</book>

<Book>

<Name> Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean </name>

</book>

<Book>

<Name> Silicon Valley Gastronomic Treats </name>

</book>

<Book>

<Name> Sushi, Anyone? </name>

</book>

<Book>

<Name> The Gourmet Microwave </name>

</book>

<Book>

<Name> The Psychology of Computer Cooking </name>

</book>

</publisher>

<Book>

<Name> But Is It User Friendly? </name>

</book>

<Book>

<Name> Cooking with Computers: Surreptitious Balance Sheets </name>

</book>

<Book>

<Name> Net Etiquette </name>

</book>

<Book>

<Name> Secrets of Silicon Valley </name>

</book>

<Book>

<Name> Straight Talk About Computers </name>

</book>

<Book>

<Name> The Busy Executive's Database Guide </name>

</book>

</publisher>

</root>

Data presentation is not so convenient for perception{recognition}, but for processing by analyzer XML what structure at the document has no value. Let's change search so what to translate the subordinated elements at <Book> in attributes. For this purpose we shall simply clean{remove} from search Element directive.

cmd. CommandText = " select 1 AS Tag, null as Parent, pub_id AS [the Publisher! 1! Number{room}], NULL AS [the Book! 2! The name], NULL AS [the Book! 2! The price], NULL AS [the Book! 2! Sales] from titles UNION SELECT 2 AS Tag, 1 AS Parent, pub_id, title, price, ytd_sales FROM titles ORDER BY [the Publisher! 1! Number{room}] FOR XML EXPLICIT "

Result on the screen:

<? xml version = " 1.0" encoding = "utf-8"?>

<root>

</publisher>

</publisher>

</publisher>

</root>

In the following part of clause{article}, we shall show as to transform such data to kind HTML, pseudo - Excel (SpreadSheet) with the help of patterns XSLT.