Working With Information Within The Tables / Relations Part – 7

Altering The Extensible Markup Language (XML) Information

Likely to whichever new sort of information, an individual may want to alter the Extensible Markup Language (XML) information. In the direction of altering the information, an individual can make use of the MODIFY function as given by the Extensible Markup Language (XML) information type of the SQL Server. The MODIFY function stipulates an XQUERY expression as well as a command which states the sorts of alteration which necessities to be perform. This function permits an individual to do the subsequent alteration:

· INSERT – It is castoff to insert nodes to the Extensible Markup Language (XML) in an Extensible Markup Language (XML) column / attribute or variable. An individual can make use of the subsequent command to insert any nodes in an Extensible Markup Language (XML) column / attribute or variable:

INSERT My_Expression1 ( { AS FIRST | LAST } INTO | AFTER | BEFORE My_Expression2 )

here,

o My_Expression1 – It classifies a single or many nodes to be added. It can be a persistent Extensible Markup Language (XML) case or an XQUERY expression. The expression can end in a node, as well as a text node, otherwise in a well – ordered arrangement of nodes. It cannot solve to the origin ( / – Backslash) node. When the expression consequences in a value otherwise in an order of values, the values are added as a sole text node by means of a space splitting every single value in the order. If an individual stipulate numerous nodes as persistent, the nodes are contained within the parentheses { } as well as are divided through commas ( , ). An individual cannot add varied order like as arrangement of elements, attributes, or values. When My_Expression1 tenacities to be a vacant order, then no addition happen plus no faults are given back.

o INTO – Nodes recognized by My_Expression1 are added as a straight child node of the nodes recognized through My_Expression2. If the node in My_Expression2 previously has a single or multiple child nodes, an individual should use either AS FIRST or AS LAST to stipulate at which position the new node to be inserted.

o AFTER – Nodes recognized by My_Expression1 are added as siblings straightly after the node recognized by My_Expression2. The AFTER keyword cannot be castoff when an individual wants to add attributes.

o BEFORE – Nodes recognized by My_Expression1 are added as siblings straightly before the node recognized by My_Expression2. The BEFORE keyword cannot be castoff for adding the attributes.

o My_Expression2 – It recognizes a node. The nodes recognized in My_Expression1 are added comparative to the node recognized by My_Expression2. This can be an XQUERY expression which gives back a position to a node which happens to be the presently referenced document. When numerous nodes are given back, the addition of the nodes are fails. If My_Expression2 yields a blank order, no addition happens as well as not faults are reported. My_Expression2 cannot be a handled as an attribute, comments or else as an instruction.

Think through, an instance, the management of XYZBank needs to insert extra column or attribute stipulating the gender of the customer, in the Customer . OnlineDetails table or relation. The default value in the Gender column or attribute must be ‘ Not Mentioned ’. In the direction of solving this problematic situation, the database designer of XYZBank will form the subsequent command:

UPDATE Customer . Details SET Customer . OnlineDetails . MODIFY ( ‘ INSERT attribute Gender { “ Not Mentioned ” } AS FIRST INTO ( / Customer ) [ 1 ] ’ )

The path expression in the earlier instance is stipulating “[ 1 ] ” as per a stationary typing obligation. This confirms a sole mark node.

· REPLACE – It is castoff to alter the Extensible Markup Language (XML) information.

For an instance, one of the many customers of XYZBank; has thought of altering the account type from Loan to Savings. Begin a database designer; an individual can form the subsequent command to imitate this alteration:

UPDATE Customer . Details SET Customer . OnlineDetails . MODIFY ( ‘ REPLACE VALUE OF ( Customer / @ AcType ) [ 1 ] WITH “ Savings ” ’ ) WHERE CustomerID = 3

· DELETE – It is castoff to eliminate a node from the Extensible Markup Language (XML) information. For an instance, the management of XYZBank has again thought of eliminating the Gender column or attribute from the Customer . OnlineDetails details table or relation. An individual can form the subsequent command to make this modification:

UPDATE Customer . Details SET Customer . OnlineDetails . MODIFY ( ‘ DELETE ( / Customer / @ Gender ) [ 1 ] ’ )

This is the concluding part of this “Working With Information Within The Tables / Relations” series, hope the reader has acquired some knowledge about how to work with the data in the table or relation in normal row or tuple and column or attribute wise as well as in the Extensible Markup Language (XML) wise.

Thanks.