Working With Information Within The Tables / Relations Part – 4

Removing Every Information From The Table / Relation

Begin a database designer; an individual may require removing all the information from a particular table or relation. An individual can perform this task by means of the subsequent DELETE command:

DELETE My_Table_Name

here,

· My_Table_Name – It stipulates the name of the table or relation from which an individual have to remove the information.

An individual can practice the TRUNCATE Data Modification Language (DML) command also. The code of the TRUNCATE command is as follows:

TRUNCATE TABLE My_Table_Name

here,

· My_Table_Name – It stipulates the name of the table or relation from which an individual have to remove the information.

But, TRUNCATE TABLE is implemented quicker than the DELETE command. TRUNCATE TABLE has no provision of WHERE condition. Additionally, the TRUNCATE TABLE command has no provision of firing the triggers. The minute truncate is castoff, the removed rows or tuples are not logged into the transaction log. Think through an instance, the subsequent command removes every information from the Management . LoanPending table or relation:

TRUNCATE TABLE Management . LoanPending

Handling The Extensible Markup Language (XML) Information

By the advancement of the client’s retrieving information over varied hardware as well as software podiums, a necessity has risen for a semantic which can be understood through whichever setting. This give rise to the development of a semantic known as Extensible Markup Language (XML). Extensible Markup Language (XML) is a markup programming language which is castoff to define the organization of information in a normal categorized way. The arrangement of the documents having the information is defined with the support of identifiers confined in the document. So, numerous commercial systems stock their information in Extensible Markup Language (XML) documents.

SQL Server permits an individual to keep otherwise to recover the information in the Extensible Markup Language (XML) layout. This allows the SQL Server to offer database backing to numerous types of systems. Begin a database designer; it is significant for an individual to known and to operate the Extensible Markup Language (XML) information through the SQL Server. The arrangement of the Extensible Markup Language (XML) information can be definite in the system of a subsidiary Document Type Definition (DTD) or schema.

Stowing Extensible Markup Language (XML) Information In A Table / Relation

The Extensible Markup Language (XML) information is obtainable in the arrangement of Extensible Markup Language (XML) parts otherwise the whole Extensible Markup Language (XML) documents. An Extensible Markup Language (XML) part is comprised of Extensible Markup Language (XML) information lacking a top – level section which holds the whole information. SQL Server usages Extensible Markup Language (XML) as a data type to keep the Extensible Markup Language (XML) information in its unique sate. An individual can form tables / relations or mutable by means of this data type to stock the Extensible Markup Language (XML) information. But, an individual can strip the Extensible Markup Language (XML) information as well as saves the data values in diverse columns or attributes in a ROWSET. This procedure of converting the Extensible Markup Language (XML) information into a ROWSET is known as slicing.

In SQL Server, an individual can stock the Extensible Markup Language (XML) information in the subsequent methods:

· A ROWSET

· An Extensible Markup Language (XML) column or attribute

Stowing The Extensible Markup Language (XML) Information In A ROWSET

Think through an instance; an individual have acknowledged the loan request from the customer. The loan requesting particulars are produced by the online system from the customer’s end in an Extensible Markup Language (XML) document format. Now, an individual is required to stock this information in a database table or relation. Intended for this, an individual is required to slice down the Extensible Markup Language (XML) information. The SQL Server permits an individual to slice down the Extensible Markup Language (XML) information by means of the OPENXML function along with its associated stored procedures.

Slicing an Extensible Markup Language (XML) document includes the subsequent jobs:

1. Break down the Extensible Markup Language (XML) file.

2. Obtain a ROWSET from the Tree.

3. Stock the information form the ROWSET.

4. Release the memory.

Break Down The Extensible Markup Language (XML) File

The SQL Server offers the SP_XML_PREPAREDOCUMENT system stored procedure to break down the Extensible Markup Language (XML) document. This system stored procedure analyzes the Extensible Markup Language (XML) document as well as break down it with the Microsoft Extensible Markup Language Core Services (MSXML) parser. Analyzing an Extensible Markup Language (XML) document includes authorizing the Extensible Markup Language (XML) information with the arrangement demarcated in the Document Type Definition (DTD) or schema. The analyzed document is an inside tree illustration of several nodes in the Extensible Markup Language (XML) document, like – Element, Attributes, Text and Comments.

SP_XML_PREPAREDOCUMENT gives back a handle otherwise a pointer which can be castoff to enter in the recently formed inner illustration of the Extensible Markup Language (XML) document. This handle is legal for the period of the session otherwise till the handle is cancelled through implementing SP_XML_REMOVEDOCUMENT.

Obtain A ROWSET From The Tree

Once authenticating the accurateness of the arrangement as well as comprehensiveness of information, an individual is required to obtain the information from the accessible Extensible Markup Language (XML) information. Intended for this, an individual can practice the OPENXML function to produce an in – memory ROWSET from the analyzed information. The code of the OPENXML function is as follows:

OPENXML ( IDOC INT [ IN ] , ROWPATTERN NVARCHAR [ IN ] , [ FLAGS BYTE

[ IN ] ] ) [ WITH ( SCHEMADECLARATION | My_Table_Name ) ]

here,

· IDOC – It stipulates the document handle of the inner tree illustration of an Extensible Markup Language (XML) document.

· ROWPATTERN – It stipulates the XPath design castoff to recognize the nodes (in the Extensible Markup Language (XML) document whose handle is delivered in the IDOC factor) to be handled as rows or tuples.

· FLAGS – It stipulates the plotting which must be castoff among the Extensible Markup Language (XML) information as well as the relational ROWSET, plus exactly how the roll – over column or attribute have to be complete. FLAGS are an elective factor plus can have the subsequent standards:

o 0 – It indicates to practice the DEFAULT plotting.

o 1 – It indicates to recover Attribute values.

o 2 – It indicates to recover Element values.

o 3 – It indicates to recover both Attribute as well as Element values.

· SCHEMADECLARATION – It stipulates the ROWSET schema affirmation for the columns or attributes which are to be reverted back by means of an amalgamation of column or attributes names, data types plus configurations.

· My_Table_Name – It stipulates the table or relation name which can be provided, in place of SCHEMADECLARATION, if a table or relation with the anticipated schema previously happens to be present as well as no column or attribute arrangement are necessary.

Stock The Information Form The ROWSET

An individual can practice the ROWSET formed through the OPENXML to save the information, in the identical method which an individual would practice some additional ROWSET. An individual can add the ROWSET information into stable tables or relations in a database.

Release The Memory

Once stocking the information everlastingly in the database, an individual is required to free the memory where an individual has stowed the ROWSET. Intended for this, an individual can practice SP_XML_REMOVEDOCUMENT system stored procedure.

In the upcoming part we will be going through the another sub – part of Stowing Extensible Markup Language (XML) Information In A Table / Relation which is the Stowing Extensible Markup Language (XML) Information In Extensible Markup Language (XML) Columns / Attributes and its two different sub – modules which are Stowing Un – Typed Extensible Markup Language (XML) Information and Stowing Typed Extensible Markup Language (XML) Information in details.