Learn Xml: Inserting Data into an XML Column

Irrespective of whether the XML column is typed or not typed, you can supply the value for an XML col-

umn in the following ways.

As a character or binary SQL type that is implicitly converted to XML data type.

As the content of a file.

As the output of the FOR XML with the TYPE directive that generates an XML data type instance.

The supplied value is checked for well-formedness and allows both XML documents and XML fragments

to be stored. If the data fails the well-formedness check, it is rejected with an appropriate error message.

For typed XML, the supplied value is checked for conformance to XML schemas registered with the

XML schema collection typing the XML column. The XML instance is rejected if it fails this validation.

Look at examples on the different ways of inserting values into an XML column.

To start with, the following statement inserts a new row into the Employee table with the value 1 for the

integer column ID and an <employee> instance for the xml_data column. The <employee> data, supplied

as a string, is implicitly converted to XML data type and checked for well-formedness during insertion.

INSERT INTO Employee values (2, ‘<employee id=”2”><name>Joe</name></employee>’)

It is also possible to utilize the contents of an XML file as an input to the Insert command. Consider the

following XML document stored in a file called Employee.xml.

</employee>

Now if you execute the following T-SQL command, you will see the contents of the Employee.xml file

being loaded into the xml_data column.

INSERT INTO Employee SELECT 7, xml_value FROM

(SELECT * FROM OPENROWSET (BULK ‘C:\Data\Employee.xml’,

SINGLE_BLOB) AS xml_value) AS R(xml_value)

The third option is to utilize the output of the FOR XML with the TYPE directive as an input to the insert

command. With SQL Server 2005 FOR XML has been enhanced with a TYPE directive to generate the result

as an XML data type instance. The resulting XML can be assigned to an XML column, variable, or parame-

ter. In the following statement, the XML instance generated using FOR XML TYPE is assigned to an XML

data type variable @var. Then the variable is used in the insert statement.

DECLARE @var xml

SET @var = (SELECT xml_data FROM Employee FOR XML AUTO,TYPE)

--Insert the value of the variable into a new table named EmployeeOutput

CREATE TABLE EmployeeOutput (xml_data xml)

INSERT INTO EmployeeOutput (xml_data) VALUES (@var)

lanka sri