XML Data Type C# Help

One of the major programming features of SQL Server is the XMLdata type, With older versions of SQL Server, XMLdata is stored inside a string or a blob. Now XML is a supported data type that allows you to combine SQL queries with XQuery expressions to search within XMLdata. An XMLdata type can be used as a variable, a parameter, a column, 01; a return value from a UDF.

With Office 2007, it is possible to store Word and Excel documents as XML.Word and Excel also support using custom XML schemas, which only the content (and not the presentation) is stored with XML.

The output of.Vffice applications can be etored directly in SQLServer, where it is possible to search within this data, Of course, custom XMLdata can also be stored in SQLServer.

Don’t use XML types for relational data. If you dp a aearch for some of the elements
if the I(hema is clearly defined for the data, storing these eIements in a relation fashion allows the data can be accessed faster, If the data is.hierarchical and some elements are optional and may change over time, storing XML data has many advantages.

Tables with XML Data

Creating tables with XMLdata is as simple as selecting the Xml data type with a column, The following  CREATE TABLE SQLcommand creates the Exams table with a column ID that is also the primary key, the column Number, and the column Info, which is of type XML.
CREATE TABLE [cibolo [Exams) (
[Id] tint] IDENTITY(l.l) NOT NULL.
[Number] [nchar) (10) NOT NULL,
[Info] [xml] NOT NULL.
CONSTRAINT [PK_Exams] PRIMARY KEY CLUSTERED
(
[Id] ASC
) ON [PRIMARY]
ON [PRIMARY]

For a simple test, the table is filled with this data:

Reading XML Values

You can read the XMLdata with AOO.NET using a SqlDataReader object. The SqlDataReader method GetSqlXml () returns a SqlXml object. The SqlXml class has a property Val ue that returns the complete XMLrepresentation and a CreateReader () method that returns an XmIReader object, The Read () method of the XmlReader is repeated in a while loop to read node by node. With the output there’s interest only in information about the value of the attribute Number, and the values of the elements Ti tIe and Course. The node to which the reader is positioned is compared with the corresponding XML element names, and the co:responding values are written to the console.

Running the application you will get the output as shown:
Exam: 70-536
Title: TS: Microsoft .NET Framework 2.0 – Application Development Foundation
Course(s): 2956 2957
Exam: 70-528
Title: TS: Microsoft .NET Framework 2.0 – Web-Based Client Development
Course(s): 2541 2542 2543 2544
Exam: 70-526
‘ritle: TS: Microsoft .NET Framework 2.0 – Windows-Based Client Developme~t’. I \,’ \
Course(s) :.~541 2542 2546 2547
Instead of using the XmlReader class you can read the complete XMLcontent into the XmlDocument class and parse the elements by using the OOM model. The method SelectSingleNode () requires an XPath expression and returns an XmlNode object. The’XPath expression I I Exam loolcs for the Exam XML element inside the complete XMLtree. The XmlNode object returned can be used to read the children of the represented element. The value of the Number attribute is a&:essed to write the exam number to the console,.then the Title element is accessed and the content of the Title element is written to the console, and the content of all Course elements is written to the console as well.

The XmIReader and XmlDocurnent classes are discussed in Chapter 28, “Manipulating XML.”

With .NET3.5 there’s another option to access the XMLcolumn from the database. You can combine LINQ to SQLand LINQ to XML,which makes the programming code smaller, You can use the LINQ to SQLdesigner by selecting the LINQ to SQLClasses template from the Data templates category.

Name the file CSharp. dbml to create a mapping for the database CSharp, Create the mapping by dragging and dropping the Exams table from the Solution Explorer to the design surface as shown in Figure 30-4.

Figure 30-4

Figure 30-4

The mapping class that is created by the designer has the name ProCSharpDataContext and defines property Exams to return all exam rows. Here a foreach statement is used to iterate through all records, Of course you can also define a LINQ query with d where expression if not all records are required, Examclass defines the properties Id, Number, and Info accordingly to the columns in the database The Info property is of type XDocurnent and thus can be accessed by using the new LINQ to XML classes from the namespace System. Xml.Linq. Invoking the method Element () passing the name the XMLelement Examreturns an XElement object that is then used to access the values of the attribute Number and the elements Ti tIe and Course in a much simpler way, as was done earlier with the XmlDocumentclass.

Query of Data

Up until now, you haven’t seen the really great features of the XML data type. SQL SELECT statements can be combined with XML XQuery.

A SELECTstatement combined with an XQuery expression to read into the XML value is shown here:

SELECT [Id], [Number], .[Info].query(‘/Exam/Course’) AS Course FROM[E;xamS].
The XQuery expression /Exam/Course accesses the Course elements that are children of the Exam element. The result of this query returns the IDs, exam numbers, and courses:
1 70-~36 <Course>2956</Cou~e><Course>2957</Course>
2 70-~8 <Course>2541</Course><Course>2542</Course><Course>2543</Course>
<Course>2544</Course>
3 70-526 <Course>2541</Course><Course>2542</Course><Course>2546</Course>
<Course>2547</Course>
With an XQUery expression, you).create more complex statements to query data within the·XML
content of a cell. The next example converts the XML from the exam information to XML that lists .
information about courses: .
SELECT [Info]. query ( ,
focus course in /Exam/Course
return
<Course>
<Exam>{ data{/Exam]/@Number) }</Exam> •
<Number>{ data ($course) }</Number>
</Course>’~~
AS Course -,
FROM[Exams]
WHEREId=2
Here, just a single row is selected with SELECT [Info FROM Exams WHERE Id = 2. With the result of this SQL query, the for and return statements of an XQuery expression are used, for course in IExam/Course iterates through all Course elements. $course declares a variable that  set with every iteration (similar to a C#-foreach statement), Following the return statement, the result of the query for every row is defined, The result for every course element is surrounded by the <Course> element. Embedded inside the <Course> element are <EXam> and <Number>, The text within the <Exam> element is defined with data (/Examll] /@Number).data() is an XQuery function that returns the value of the node specified with the argument. The node IExam Il] is used to access the first <Exam> element; iNumber specifies the XMLattribute Number. The text within the element <Nuiaber> is defined from the variable coursE Contrary to Of, whAt the first element in a collection is accessed with an index of 0, with XPath the first element in a colltction is accessed with an index of 1.

The result of this query is shown here:
<Course>
<Exam>70-S28</Exam>
<Number>2S41</Number>
</Course>
<Course>
<Exam>70-S28</Exam>
<Number>2S42</Number>
</Course>
<Course
<Exam>70-528</Exam>
<Number>2543</Number>
</Course>
<Course>
<Exam>70-528</Exam>
<Number>2544</Number>
</Course>
You can change the XQuery statement to also include a where clause for filtering XMLelements, The following example only returns courses from the XMLcolumn if the course number has a value higher
than 2542:
SELECT (Info] .query (,
for $course in /Exam/Course
where ($course > 25421
return
<Course>
<Exam>( data (lExamll] /iNumber) }</Exam>
<Number>{ data ($course) }</Number>
</Course>’ )
AS Course
FROM IExams)
WHERE Id=2
The result is reduced to just two c~urse numbers:
<Course>
<Exam>70·52B</Exam>
<Number>2543</Number>
</Course>
<Course>
<Exam>70-52B</Exam>
<Number>2S44</Number>
</Course>
XQuery in SQLServer allows using several other XQuery functions for getting minimum, maximum, or summary values, working with strings, numbers, checking for positions within collections, and so on.

The next example shows the use of the count () function to get the number of /Exam/Course elements:
SELECT [Idl. [Nwnberl. [Infal.query(‘
count (/Exam/Course) , )
AS ‘Course Count”
FROM[Exams I
The data returned displays the number of courses for the exams·
Id     Number                           Course Count
1      70-536                                    2
2      70-528                                    4
3      70-526                                    4

XML Data Modification Language (XML DML)

XQuery as it is defined by the W3C (http:www.Csharpaid.com) allows only querying of data. Because of this XQuery restriction, Microsoft defined an extension to XQuery that has the name XML Data Modification Language (XML DML), XML DML makes it possible to modify XML data with the following XQuery extensions: insert, delete, and replace value of.

UPDATE[Exams]
SET [Info] .modify(‘
insert <Course>2555</Course> as last into Exam[l] ‘)
WHERE[;:I’d] =3
XML content can be deleted with the delete keyword. Within the first Exam element, the last Course
element is deleted. The last element is selected by using the last () function.
UPDATE[Exams]
SET [Info]. modify ( , •
delete IExam[l]/Course[last()] ‘)
FROM[Exams] WHERE[Id]=3
It is also possible to change XML content. Here, the keyword replace value of is used. The
expression IExam/Course [text () = 25431 accesses only the child elements Course where the text
content contains the string 2543. From these elements, only the text content is accessed for replacement
with the text () function. If only a single element is returned from the query, it is still required that you
specify just one element for replacement. This is why explicitly the first text element returned is specified
with [11.2599 speciries that the new course number is 2599:
UPDATE exams
SET [Info]
replace va’
FROM[Exams
of (/Exam/Course[text() 25431/text()) [II with 2599′)
This section looks at some examples to insert, delete, and modify XML contents within a cell.

You can use the insert keyword to insert some XML content within an XML column without replacing the complete XML cell. Here, <Course>2555</Course> is inserted as the last child element of the first Exam element:

XML Indexes

If some specific elements are often searched within the XML data, you can specify indexes within the XML data h.e. XML indexes must be distinguished as being a primary or a secondary XML index type, A primary  ‘1 index is created for the complete persisted representation of the XML value.

The following SQL command, CREATE PRIMARYXML INDEX,creates the index irncexams on the Info column:
CREATEPRIMARYXMLINDEX irncexams on Exams (Info)

Primary indexes don’t help if the query contains an XPath expression to directly access XML elements of the XML type, For XPath and XQuery expressions, XML secondary indexes can be used, If an XML secondary index is created, the primary index must already exist. With secondary indexes, these index types must be distinguished:

  1. PATH index
  2. VALUE index
  3. PROPERTY index

A PATHindex is used if exists () or query () functions are used and XML elements are accessed with an XPath expression. Using the XPath expression I Exam/Course, it might be useful to do a PATHindex:
CREATEXMLINDEX idx_examNumbers on [Exams] (Info)
USING XMLINDEX idx_exams FOR PATH
The PROPERTYindex is used if properties are fetched from elements with the val ue () function. The FOR
PROPERTYstatement with the index creation defines a PROPERTYindex:
CREATEXMLINDEX idx_exa!l1Numbers on [Exams] (Info)
USING XMLINDEX idx_exams FOR PROPERTY
If elements are searched through the tree with an XPath descendant-or-self axis expression, the best performance might be achieved with a VALUEindex, The XPath expression I ICertification searches all Cert i fica tion elements with the descendant-or-selfaxis. The expression [@Name=’MCTS Web Applications’] returns only the elements where the attribute Name has the value MCTS Web Applications: SELECT [Info).query(‘/Exam/Title/text() ‘) FROM[Exams]
WHERE[Info] .exist(‘//Certification[@Name=’MCTS Web Applications’] ‘) 1
The result returned lists the titles of the exams that contain the requested certification:
TS: Microsoft .NET Framewprk 2.0 – Application Development Foundation
TS: Microsoft .NET Framework – Web-Based Client Development
The VALUEindex is created with the FOR VALUEstatement:
CREATEXMLINDEX idx_examNumbers on [Exams] (Info)
USING XMLINDEX idx_exams FOR VALUE

Strongly Typed XML

The XML data type in SQL Server can also be strongly typed with XML schemas. With a strongly typed XML column, it is verified if the data conforms to the schema when XML data is inserted.

A XML schema can be created with the CREATE XML SCHEMACOLLECTIONstatement. The statement shown here creates a simple XML schema, CourseSchema. The schema defines the type Course  that contains a sequence of Number and Title, which are both of t} pe string, and an element Any, which can be any type. Number and Ti tIe may occur only once. Be’ ause Any has the minOccurs attribute set to 0, and the maxOccurs .attribute set to unbounded, this element is optional. This allows you to add any additional information to the CourseEl t type in future versions while the schema still remains valid, Finally, the element name Course is of type CourseElt.

<Title>Advanced Distributed Application Development with Visual Studio 2005Title> </Course>
With the Visual Studio Database project type, there’s no support to add a schema to the database.

This feature is not available from the GUI by Visual Studio 2008 but must be created manually, To create an XML schema with Visual Studio 2008, create a new Visual Studio project by using the Empty Project template. )(dd a new XML schema to the project, Then copy the XML syntax of the schema into the CREATE XML SCHEMA statement.

Besides using VisualStudio, you can copy the XML syntax into SQL Server Management Studio to create and view the XML schemas (see Figure 30-5). The Object Explorer lists the XML schemas under the Types entry.

The XML schema can be assigned to a column by setting it with the xrnl. data type:
CREATE TABLE [Courses)

creating the table with Visual Studio 2008 or withSQL Server Management Studio, the XML schema
be assigned to a column by setting the property XML schema namespace
Now as you add data to the XML column, the schema is verified. H the XML does not satisfy the schema definition, a SqlException is thrown with an XML Validation error.

Figure 30-5

Figure 30-5

Posted on October 31, 2015 in .NET Programming with SQL Server

Share the Story

Back to Top