Working With Information Within The Tables / Relations Part – 5

Stowing Extensible Markup Language (XML) Information In Extensible Markup Language (XML) Columns / Attributes

From time to time, an individual is required to stock the Extensible Markup Language (XML) information in its unique state in a column or attribute of a database table or relation.

Think through, an instance, an individual is required to keep the particulars of a customer in the database. The particulars of the specific customers are preserved through a website. The website keeps the particulars of every single customer in an Extensible Markup Language (XML) file. Begin a database designer; an individual is required to keep this particular information in the SQL Server. Intended for this, an individual can build the subsequent table or relation to stock the customer particulars:

CREATE TABLE Customer . OnlineDetails ( CustomerID INT , CustDetails XML )

An individual can keep the subsequent sorts of information in the columns or attributes through the Extensible Markup Language (XML) data types:

· Un – Typed Extensible Markup Language (XML) Information – It is a fine – shaped information too, nevertheless it is not related with any schema. The SQL Server do not authenticate this information, however confirms that the information which is kept with the Extensible Markup Language (XML) data type is fine – shaped.

· Typed Extensible Markup Language (XML) Information – It is a fine – shaped information which is related with some schema demarcating the element plus their attributes. It stipulates a namespace for the information too. While an individual keep the Typed Extensible Markup Language (XML) information in a table or relation, the SQL Server authenticates the information in contradiction of the schema plus allocates the suitable data type to the information grounded on the data types demarcated in the schema. This aids in protecting the storage space.

Begin a database designer; an individual must be familiar with in what manner to stock both kinds of information on the SQL Server.

Stowing Un – Typed Extensible Markup Language (XML) Information

In the direction of stowing the Un – Typed Extensible Markup Language (XML) information, an individual can make use of the columns / attributes or variables which are having the Extensible Markup Language (XML) data type. For an instance to keep the online customer information in the Customer . OnlineDetails table or relation, an individual can practice the subsequent INSERT command:

INSERT INTO Customer . OnlineDetails VALUES ( 5 , ‘ < Customer Name = “ MyName ” Phone = “ 0987654321 ” AcType = “ Loan ” / > ’ )

In the previous command, the string value which has an Extensible Markup Language (XML) portion is discreetly transformed to Extensible Markup Language (XML). But, an individual can transform a string value to Extensible Markup Language (XML) by means of the CONVERT or else CAST functions. In this instance, an individual can practice the subsequent command to transform the data type of the string value to Extensible Markup Language (XML) earlier to adding the information in the table or relation:

INSERT INTO Customer . OnlineDetails VALUES ( 3 ,CONVERT ( XML , ‘ < Customer Name = “ OurName ” Phone = “ 9687667721 ” AcType = “ Loan ” / > ’ ) )

In the same way, an individual can practice the CAST function too, as stated in the subsequent command:

INSERT INTO Customer . OnlineDetails VALUES ( 5 , CAST ( ‘ < Customer Name = “ YourName ” Phone = “ 8564839575 ” AcType = “ Savings ” / > ’ AS XML ) )

Stowing Typed Extensible Markup Language (XML) Information

In the direction of stowing the Typed Extensible Markup Language (XML) information, an individual is required to register the schema related by the information in the Extensible Markup Language (XML) schema assembly items in the database at first. The Extensible Markup Language (XML) schema assembly is an item on the SQL Server which is castoff to keep more than one schemas. An individual can form an Extensible Markup Language (XML) schema assembly item by means of the subsequent command:

CREATE XML SCHEMA COLLECTION < Identifying_Name > AS Expression

here,

· Identifying_Name – It stipulates a mutable name through which the SQL Server will recognize the schema assembly.

· Expression – It stipulates an Extensible Markup Language (XML) value which has single or else multiple Extensible Markup Language (XML) schema documents.

An individual can observe the data about the listed schemas in a database through enquiring the SYS.XML_SCHEMA_COLLECTIONS collection view, as displayed in the subsequent command:

SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS

Once registration of the Extensible Markup Language (XML) schema is completed, an individual can make us of the schemas to authenticate the typed Extensible Markup Language (XML) values at the time of adding the information in the tables or relations. An individual is required to state this at the time of forming a table or relation which will stock the Extensible Markup Language (XML) information.

In the upcoming part we will be going through the Recovering The Extensible Markup Language (XML) Information From A Table / Relation and its sub – parts which are By The FOR XML Section In The SELECT Command along with sub – method which are Via The RAW Method, Via The AUTO Method, Via The PATH Method, and Via The EXPLICIT Method additionally the another sub – part which is the By The XQUERY in details.