Working With Information Within The Tables / Relations Part – 6

Recovering The Extensible Markup Language (XML) Information From A Table / Relation

From time to time, an individual is required to recover the relational information from a table or relation into the Extensible Markup Language (XML) layout for producing reports or to share the information through diverse systems. This includes obtaining the information form a table or relation in the form of fine – shaped Extensible Markup Language (XML) parts. An individual can recover the Extensible Markup Language (XML) information in the subsequent methods:

· By the FOR XML section in the SELECT command

· By XQuery

By The FOR XML Section In The SELECT Command

SQL Server permits an individual to obtain the information from relational tables or relations into an Extensible Markup Language (XML) layout by means of the SELECT command through the FOR XML section. An individual can make use of the FOR XML section to recover the Extensible Markup Language (XML) information by means of the subsequent methods:

· RAW

· AUTO

· PATH

· EXPLICIT

Via The RAW Method

The RAW method is castoff to yield an Extensible Markup Language (XML) file with every single row or tuple signifying an Extensible Markup Language (XML) element. The RAW method converts every single row or tuples in the enquiry result set into an Extensible Markup Language (XML) element by the element name row or tuple. Every single column or attribute value which is not NULL is plotted to an attribute with the similar name as the column or attribute name. If the ELEMENT command is stated with the FOR XML section’s RAW method, every single column or attribute value is plotted to a sub – element of the < ROW > element.

Via The AUTO Method

The AUTO method is castoff to yield enquiry results as nested Extensible Markup Language (XML) elements. Same like the RAW method, every single column or attribute value which is not NULL is plotted to an attribute which is named after either the column or attribute name or the column or attribute alias. The element to which these attributes fit in is named to the table or relation which they feel right to or the table or relation alias which is castoff in the SELECT command. If the noncompulsory ELEMENT command is stated in the FOR XML section’s AUTO method, the columns or attributes itemized in the SELECT section are plotted to sub – elements.

Via The PATH Method

The PATH method is castoff to yield exact values through signifying the column or attribute names for which an individual is required to recover the information. An individual can make use of the noncompulsory ElementName argument through the PATH method enquiry to alter the name of the default row or tuple element also.

Via The EXPLICIT Method

The EXPLICIT method is castoff to yield an Extensible Markup Language (XML) file which gets the arrangement as stated in the SELECT command. Parted SELECT commands can be united by the UNION ALL command to produce every single level or element in the consequential Extensible Markup Language (XML) output. Every single SELECT command necessitates the main two (2) identifiers which are to be termed as TAG and PARENT. The PARENT element is castoff to regulate the combination of elements. It has the identifier number of the parent element of the existing element. The highest – level element in the document must have the PARENT value fixed to 0 or NULL.

Think through an instance, the managers of the XYZBank database need to use the data concerning the different loans over their mobile gadgets. These gadgets cannot openly link to the SQL Server; however it can get the information given in the Extensible Markup Language (XML) setup. So, an individual is required to transform the particulars of the loan available from the Management . LoanDetails table or relation to the Extensible Markup Language (XML) document. In the direction of doing this job, an individual is required to form an Extensible Markup Language (XML) document with < Loan > as the PARENT identifier. The < Loan > identifier will have LoanID as a column or attribute in addition to < LoanType > plus < ROI > as the child elements. In the direction of implementing this job, the database designer can form the subsequent command:

SELECT 1 AS TAG , NULL AS PARENT , LoanID AS [ LoanDetails ! 1 ! LoanID ] , LoanType AS [LoanDetails ! 1 ! Type ! ELEMENT ] , Interest_Rate AS [LoanDetails ! 1 ! ROI ! ELEMENT] FROM Management . LoanDetails FOR XML EXPLICIT

By The XQUERY

Additionally to the FOR XML, SQL Server permits an individual to get the information kept in variables or columns / attributes by the Extensible Markup Language (XML) information kind by means of XQUERY. XQUERY is a semantic which practices a group of commands as well as functions delivered through the Extensible Markup Language (XML) data type to get the information. As paralleled to the FOR XML section of the SELECT command, the XQUERY command permits an individual to get the exact portions of the Extensible Markup Language (XML) information. Every single XQUERY command contains of two (2) fragments, introduction plus body. In the introduction segment, an individual states the namespaces. Moreover, schemas can be brought in the introduction section. The body fragment stipulates the Extensible Markup Language (XML) nodes to be recovered. The XQUERY semantic contains the subsequent commands:

· FOR – It is castoff for repeating over a group of nodes at the similar level as in an Extensible Markup Language (XML) document.

· LET – It is castoff for stating the variables as well as for passing on the values.

· ORDER BY – It is castoff for stipulating an order.

· WHERE – It is castoff for stipulating any standards meant for the information that is to be recovered.

· RETURN – It is castoff for stipulating the Extensible Markup Language (XML) reverted back from any command.

The XQUERY command makes use of the subsequent functions as delivered through the Extensible Markup Language (XML) data type:

· QUERY – It is castoff for extracting the Extensible Markup Language (XML) form an Extensible Markup Language (XML) information type. The Extensible Markup Language (XML) that is to be recovered is stated through the XQUERY expression accepted as a constraint.

· VALUE – It is castoff for yielding back a sole value from an Extensible Markup Language (XML) document. In the direction of recovering that sole value, an individual is required to stipulate an XQUERY expression which classifies a sole node as well as a data type of the value that is to be recovered.

· EXIST – It is castoff for inspecting the presence of a node in Extensible Markup Language (XML) information. The function gives back 1 when the stated node is present or else it gives back 0. For an instance, the management of the XYZBank; needs the particulars of every customer who are having a loan account. The particulars of every customer are stowed in an Extensible Markup Language (XML) setup in the Customer . OnlineDetails table or relation. An individual can make use of the subsequent command for showing the output:

SELECT CustomerID , CustDetails . EXIST ( ‘ Customer [ @ AcType = “ Loan ” ] ’ ) AS ‘ TRUE ’ FROM Customer . OnlineDetails

In the upcoming part we will be going through the Altering The Extensible Markup Language (XML) Information in details discussing the Insert, Update, Delete operation as well.