Delen via

  • Artikel

Handling the xml Data Type and CLR User-defined Types

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

In Native XML Web Services beginning with SQL Server 2005, passing types such as the xml data type or common language runtime (CLR) user-defined types requires some additional development tasks. This topic describes the tasks you must perform to enable XML Web services-based applications to work with xml and CLR user-defined type in Web methods and parameterized queries.

Note

It is assumed that you have a basic knowledge of deploying Native XML Web Services in SQL Server. This includes being familiar with such tasks as creating endpoints, exposing SQL programmability as Web methods, and writing basic Web client applications for SQL Server that use other built-in SQL types. If want to review this information, see Native XML Web Services Concepts, Deploying Native XML Web Services, and Best Practices for Using Native XML Web Services.

Handling the xml Data Type in Web Client Applications

For a Web client application to correctly handle the xml data type, some details depend upon which of the following scenarios applies:

  • You are working with a stored procedure that is exposed as a Web method on an endpoint.

  • You are using the SQL batch (sqlbatch) functionality of the endpoint to execute a parameterized query.

Both of these scenarios require parameterized instances of the xml data type to be handled by using and filling in a myEndpoint**::xml** structure. In this structure, myEndpoint represents the actual name of the endpoint that is used when instances of the xml data type are passed through the client-side code. This structure is declared in the endpoint Web proxy class.

The myEndpoint**::xml** structure is created when you add or update the Web reference to the endpoint that exposes the Web method in the Visual Studio project. However, you will have to fill in the initial generated myEndpoint**::xml** structure in the custom Web proxy class accordingly, based on whether you are using typed XML or untyped XML in the client-side application code.

For untyped xml data type parameter instances in Web methods, the myEndpoint**::xml** structure exposes them as an array of type System.Xml.XmlNode in the proxy class. Therefore, to pass in an xml data type parameter instance, you manually construct and fill in an array of XML nodes, or preferably use System.Xml.XmlDocumentFragment to do this. For more information, see Working with the xml Data Type in Visual Studio Client Applications.

For typed xml in Web methods, a custom type is generated in the Web proxy class and named according to the following format: the method name concatenated with the word Type and then followed by the name of the parameter. For example, if the Web method is exposed by using the name GetXmlInfo and takes an xml data type parameter named T for passing typed XML as input, the name of the exposed custom type in the Web proxy class will be GetXmlInfoTypeT. This custom type inherits from the myEndpoint**::xml** structure, therefore it similarly exposes the typed XML as an array of System.Xml.XmlNode.

Handling the xml data type in parameterized queries is similar to working with the xml data type in Web methods with one exception: the typed XML must be passed from the client by using the same type (myEndpoint**::xml**) that is used with untyped XML.

After the myEndpoint**::xml** structure is prepared, the xml data type instance can then be exposed as an array of System.Xml.XmlNode within the defined structure, and this is in turn included within the SqlParameter.Value object.

A parameterized query requires SQL batch functionality. This can involve the following additional preparations:

  • The endpoint must have SQL enabled on it. This means that BATCHES=ENABLED was used when the endpoint was created or modified.

  • In the Web proxy class, the sqlbatch() method will be included when a Web reference is added or updated for a batch-enabled endpoint.

For typed XML parameters, the sqlbatch() method in the Web proxy class is updated to include the setting of any additional properties (XmlSchemaCollectionDatabase, XmlSchemaCollectionName, XmlSchemaCollectionOwningSchema) that are related to registering an XML schema collection for the System.Data.SqlClient.SqlParameter object.

Note

For both Web methods and parameterized queries that expose the xml data type, where a System.Data.DataSet is returned in output (as part of an array of objects) and its contents is placed in a DataGrid to visualize the results in the client application, the DataSet does not use Web proxy type (myEndpoint::xml) but the CLR System.Data.SqlTypes.SqlXml type instead.

Handling CLR User-defined Types with Web Client Applications

To handle CLR user-defined types in a Web client application, you must complete the following steps:

  1. Write the CLR user-defined type and compile it in a DLL, such as MyType.dll.

    In Visual Studio 2005, write the CLR user-defined type (class or struct) and compile it to an assembly. The type assembly must comply with SQL Server requirements for implementing user-defined types. This enables the assembly to be installed and registered on an instance of SQL Server. For more information, see "Requirements for Implementing UDTs" in CLR User-Defined Types.

  2. To generate an XML Serializer companion DLL, if you have not implemented IXMLSerializable, run Sgen.exe on the type assembly DLL. This will have a name such as MyType.XmlSerializers.dll.

    Besides meeting the base requirements for the CLR user-defined type to work with SQL Server, the CLR user-defined type must also be XML serializable to work with Native XML Web Services in SQL Server. For more information, see "XML Serialization" in CLR User-Defined Types.

  3. Install the type assembly DLL at the instance of SQL Server by using CREATE ASSEMBLY.

    If you did not implement IXMLSerializable and completed step 2, you will also have to install the XML Serializer companion DLL at the instance of SQL Server by using CREATE ASSEMBLY.

  4. Serialize the CLR user-defined type into XML and include it into a myEndpoint**::xml** structure similar to that described in the previous section.

    After the CLR user-defined type is installed at the server, to pass an instance of that CLR user-defined type into SQL Server from a Native XML Web Services client application, you first must serialize the CLR user-defined type into XML format and include it into an XML structure.

    The following code shows how to serialize a CLR user-defined type to XML format and put it into an XML element (System.Xml.XmlElement).

    // Create the user-defined type class on the client.
SqlString s = new SqlString("0:0");
UdtClientApp.Point pnt = Point.Parse(s);
// Invoke the method and pass in a user-defined type.You will need
// to convert this to XmlElement before you can pass it to SQL Server.
System.IO.MemoryStream writer = new System.IO.MemoryStream();
System.Xml.Serialization.XmlSerializer serializer = new System.Xml.Serialization.XmlSerializer(typeof(UdtClientApp.Point));
serializer.Serialize(writer, pnt);
writer.Seek(0, System.IO.SeekOrigin.Begin);
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.Load(writer);
System.Xml.XmlElement udtXml = xmlDoc.DocumentElement;

  5. Depending on whether the CLR user-defined type is present on the client, you might also have to deserialize an output parameter from its CLR user-defined type XML format back into its user-defined type format.

    The following code shows how to deserialize a user-defined type XML back to the CLR user-defined type in client-side code. In this example, the CLR user-defined type is Point.

    Object[] results = proxy.GetPointUdt(Convert.ToInt16(textBox1.Text), ref udtXml);
//Deserialze the XML into user-defined type.
TextReader reader = new StringReader(udtXml.OuterXml);
// pnt was already defined as UdtClientApp.Point pnt = Point.Parse(s);
pnt = (UdtClientApp.Point) serializer.Deserialize(reader);

    Note that you do not have to perform this deserialization process if you are using the CLR user-defined type as untyped XML on the client.

CLR user-defined types can also be passed as parameters to a parameterized query in the same way as described for the xml data type. The CLR user-defined type in its XML serialized form must be passed from the client by using the myEndpoint**::xml** type.

With a parameterized query that involves CLR user-defined types, different values are set on the System.Data.SqlClient.SqlParameter structure. For example, the following property settings are used for CLR user-defined types:

  • The SqlDbType property must be set to a value of Udt.

  • The ClrTypeName property must be set to the SQL Server three-part qualified name (MyDatabase**.MySchema.**MyUdtType) of the installed user-defined type as it is registered at the instance of SQL Server.