Section 7.2. Creating xml Data Type Instances

7.2. Creating xml Data Type Instances

You can create instances of XML data by casting or converting from strings. You can even take advantage of implicit casting by simply using a string in place of an xml type value. The next two subsections go into more detail on these topics, and the third shows you how to bulk load XML data. A fourth way to create a value of xml type is to issue a SELECT statement with a FOR XML clause. You'll find that method discussed in detail in "XML Results Using the FOR XML Clause" later in this chapter.

7.2.1. Casting and Converting Strings

You can cast or convert (CONVERT function) any string data type instance[n][var]char, [n]text, varbinary, and imageto an xml data type instance. Untyped data is checked to ensure that it is well formed. Instances of typed XML data are validated against the associated schema.

The XML parser discards insignificant whitespace when converting string data types to xml data types when either of the following is true:

• The xml:space attribute is not defined on an element or its ancestors.

• The xml:space attribute defined on an element or one of its ancestors has the value of default.

You can override the default whitespace handling behavior by setting the optional style (third) parameter of the CONVERT function to 1. You cannot override the default whitespace handling when using the CAST function to cast a string data type instance to an xml data type instance.

The following example inserts a row into the xmlTable table created in the "Creating xml Data Type Columns and Variables" section earlier in this chapter. The example specifies the style attribute (third argument) of the CONVERT function to preserve whitespace when converting a string to an xml data type instance.

     USE ProgrammingSqlServer2005     INSERT INTO xmlTable (ID, xmlCol)     VALUES (2, CONVERT(xml, '<rootNode2>  <childElement/>  </rootNode2>', 1)) 

If the style argument is not specified or set to 0, the xml data type instance will be stripped of whitespace and stored as follows:

     <rootNode2><childElement/></rootNode2> 

7.2.2. Constant Assignment

A string constant can be used where an xml data type instance is expected. An implicit cast to the xml data type is performed. If the xml data type is typed, the XML in the string is validated against the associated XML schema. The following example implicitly casts a string to an xml data type variable:

     DECLARE @xmlVar xml     SET @xmlVar = '<rootNode><childElement/></rootNode>'     SELECT @xmlVar 

The xml data type instance is returned as shown in Figure 7-3.

Figure 7-3. Results for xml constant assignment example

7.2.3. Bulk Loading Data with OPENROWSET

Enhanced OPENROWSET functionality in SQL Server 2005 lets you bulk load XML from files into xml data type columns. The syntax for the OPENROWSET statement for bulk loading data is as follows:

     OPENROWSET     (BULK 'data_file',            { FORMATFILE = 'format_file_path' [ <bulk_options> ]            | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }     } )     <bulk_options> ::=        [ , CODEPAGE   = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }]        [ , ERRORFILE   = 'file_name' ]        [ , FIRSTROW   = first_row ]        [ , LASTROW   = last_row ]        [ , MAXERRORS   = maximum_errors ]        [ , ROWS_PER_BATCH   = rows_per_batch ] 

where:

BULK 'data_file'

Uses a BULK rowset provider from the data file with the full path specified by data_file.

FORMATFILE

Specifies the full path of the format filea file that defines column types in the result set. XML and non-XML format file types are supported. The non-XML format file is the same as that used with bcp.exe or the BULK INSERT statement. See Microsoft SQL Server 2005 Books Online for more information.

The format file is not needed when SINGLE_BLOB, SINGLE_CLOB, or SINGLE_NCLOB is specified.

< bulk_options>

Specifies one or more of the following options for the BULK option:

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | ' code_page' }

The codepage of the data in the data file. CODEPAGE is relevant only if the data contains char, varchar, or text columns with character values less than 32 or greater than 127.

ERRORFILE

The full path to the file used to log nonconforming rowsrows that are not loaded.

FIRSTROW

The number of the first row to load. The default value is 1, meaning the first row in the data file.

LASTROW

The number of the last row to load. The default value is 0, meaning the last row in the data file.

MAXERRORS

The maximum number of syntax errors or nonconforming rows before OPENROWSET returns an error. The default value is 10.

ROWS_PER_BATCH

OPENROWSET always bulk loads a data file as a single batch. ROWS_PER_BATCH specifies the approximate number of rows in the data file and is used by the query processor as a hint for allocating resources in the query plan. The default value is 0, meaning that ROWS_PER_BATCH is not known.

SINGLE_BLOB

Returns the contents of the data file as a single-column, single-row result set of the varbinary(max) data type.

SINGLE_CLOB

The data file is read as ASCII and returned as a single-column, single-row result set of the varchar(max) data type.

SINGLE_NCLOB

The data file is read as UNICODE and returned as a single-column, single-row result set of the nvarchar(max) data type.

The following example bulk loads data from the following tab-delimited file OpenRowSetData.txt. Make sure that the ID and ValueCol values are separated with a tab.

     1    Value 1     2    Value 2     3    Value 3     4    Value 4 

First create the table, OpenRowSetTable, that will be the destination for the bulk-loaded data by executing the following statement:

     USE ProgrammingSqlServer2005     CREATE TABLE OpenRowSetTable     (       ID int,       ValueCol varchar(50)     ) 

Create a non-XML format file named OpenRowSetData.fmt that describes the data being bulk loaded:

     9.0     2     1   SQLCHAR   0   12   "\t"     1   ID         ""     2   SQLCHAR   0   50   "\r\n"   2   ValueCol   SQL_Latin1_General_CP1_CI_AS 

Executing the following OPENROWSET statement loads the data using the non-XML format file into the OpenRowSetTable table:

     INSERT INTO OpenRowSetTable(ID, ValueCol)     SELECT rs.ID, rs.ValueCol     FROM OPENROWSET( BULK N'C:\PSS2005\OpenRowSet\OpenRowSetData.txt',        FORMATFILE = N'C:\PSS2005\OpenRowSet\OpenRowSetData.fmt') AS rs 

Examining the OpenRowSetTable table reveals four rows, as shown in Figure 7-4.

Next, you will bulk load the data from the text file to the OpenRowSetTable table using an XML format file. Create the following XML format file and name it OpenRowSetData.xml:

Bulk Copy Format Files

You need a bulk copy format file to bulk load data in a file to columns in a table. The format file defines the file layout and other characteristics of the data in the file. There are two format file styles: non-XML and XML.

You can use the bcp utility to generate either a non-XML or an XML format file. The following statement generates a non-XML format file for the table OpenRowSetTable created in this section:

     bcp.exe ProgrammingSqlServer2005.dbo.OpenRowSetTable format nul -f     OpenRowSetData.fmt -T -c 

where the options are the following:

<databaseName.schemaName.tableName>

Specifies the fully qualified table from which the format file is generated.

format nul -f <filename>

Instructs the bcp utility to generate a non-XML format file.

-T

Specifies that the bcp utility connects to the SQL Server using a trusted connection. If -T is not specified, you must specify -U and -P (username and password) to log in.

-c

Specifies that the data in the bulk load file is character data. -n is used to specify native types.

Adding the -x flag to the preceding bcp.exe command generates an XML format file:

     bcp.exe ProgrammingSqlServer2005.dbo.OpenRowSetTable format nul -f     OpenRowSetData.fmt -x -T -c 

For more information about using the bcp utility to generate format files, see Microsoft SQL Server 2005 Books Online.

     <?xml version="1.0"?>     <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/       format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">      <RECORD>       <FIELD  xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>       <FIELD  xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="50"         COLLATION="SQL_Latin1_General_CP1_CI_AS"/>      </RECORD>      <ROW>       <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLINT"/>       <COLUMN SOURCE="2" NAME="ValueCol" xsi:type="SQLVARYCHAR"/>      </ROW>     </BCPFORMAT> 

Bulk load the data using the XML format file into the OpenRowSetTable table using the same statement as before but specifying the XML format file:

     INSERT INTO OpenRowSetTable(ID, ValueCol)     SELECT rs.ID, rs.ValueCol     FROM OPENROWSET( BULK N'C:\PSS2005\OpenRowSet\OpenRowSetData.txt',        FORMATFILE = N'C:\PSS2005\OpenRowSet\OpenRowSetData.xml') AS rs 

Figure 7-4. Results for bulk load example

The OpenRowSetTable now contains eight rows.