At times, Programmer need to store the XML data in its original state in a column of a database table. For example, you need to save the details of customers in the database. The details of individual customers are maintained by a website. The website saves the details of each customer in an XML file. As a database developer, you need to save this data in the SQL Server. For this, you can create the following table to store the customer details:
CREATE TABLE CustDetails ( CUST_ID int, CUST_DETAILS XML )
You can save the following types of data in the columns with the XML data types:
Untyped XML data: is also a well-formed data, but is not associated with a schema. The SQL Server does not validate this data, but ensures that the data being saved with the XML data type is well-formed.
Typed XML data: is a well-formed data that is associated with a schema defining the elements and their attributes. It also specifies a namespace for the data. When you save the typed XML data in a table, the SQL Server validates the data against the schema and assigns the appropriate data type to the data based on the data types defined in the schema. This helps in saving the storage space.
As a database developer, you should know how to store both types of data on the SQL Server.
INSERT INTO CustDetails VALUES (2, ‘<Customer Name=’Abrahim Jones” City= “Selina” />’)
In the preceding statement, the string value that contains an XML fragment is implicitly converted to XML. However, you can also convert a string value to XML by using the CONVERT or CAST functions. In this example, you can use the following statement to convert the data type of the string value to XML before inserting the record into the table.
INSERT INTO CustDetails VALUES (2, convert (XML, ‘<CustomerName=”Abrahim Jones” City=”Selina” />’) )
Similarly, you can also use the CAST function, as shown in the following statement:
INSERT INTO CustDetails VALUES (4, cast (‘<Customer Name=”Abrahim Jones” City=”Selina” />’ as XML) )
CREATE TABLE CustDetails ( CUST_ID int, CUST_DETAILS XML )
You can save the following types of data in the columns with the XML data types:
Untyped XML data: is also a well-formed data, but is not associated with a schema. The SQL Server does not validate this data, but ensures that the data being saved with the XML data type is well-formed.
Typed XML data: is a well-formed data that is associated with a schema defining the elements and their attributes. It also specifies a namespace for the data. When you save the typed XML data in a table, the SQL Server validates the data against the schema and assigns the appropriate data type to the data based on the data types defined in the schema. This helps in saving the storage space.
As a database developer, you should know how to store both types of data on the SQL Server.
Staring Untyped XML Data
To store the untyped XML data, you can use columns or variables with the XML data type. For example, to store customer data in the CustDetails table, you can use the following INSERT statement:INSERT INTO CustDetails VALUES (2, ‘<Customer Name=’Abrahim Jones” City= “Selina” />’)
In the preceding statement, the string value that contains an XML fragment is implicitly converted to XML. However, you can also convert a string value to XML by using the CONVERT or CAST functions. In this example, you can use the following statement to convert the data type of the string value to XML before inserting the record into the table.
INSERT INTO CustDetails VALUES (2, convert (XML, ‘<CustomerName=”Abrahim Jones” City=”Selina” />’) )
Similarly, you can also use the CAST function, as shown in the following statement:
INSERT INTO CustDetails VALUES (4, cast (‘<Customer Name=”Abrahim Jones” City=”Selina” />’ as XML) )