Subscribe To

Subscribe to SQL Server and XML by Email
Labels

Friday, November 30, 2007

XML Workshop V - Reading Values from XML Columns

If you are new to XQuery, you might find it little tricky to read values from an XML column. Reading a value from an XML variable is slightly different from reading values from an XML column. In an XML variable we have a single XML document. But the case of an XML column is different. When you query a table, each row in the table has an XML document stored in it.

Let us look at an example. In the following example, we assign a value to an XML variable and Query it.

    1 /*

    2 Let us declare an XML variable and store a value.

    3 */

    4 DECLARE @x XML

    5 SET @x =

    6 '<Name>

    7     <First>Jacob</First>

    8     <Last>Sebastian</Last>

    9 </Name>'

   10 

   11 /*

   12 Query the XML variable.

   13 */

   14 SELECT

   15     n.l.value('First[1]','VARCHAR(20)') AS First,

   16     n.l.value('Last[1]','VARCHAR(20)') AS Last

   17 FROM @x.nodes('/Name') n(l)

   18 

   19 /*

   20 OUTPUT

   21 

   22 First                Last

   23 -------------------- --------------------

   24 Jacob                Sebastian

   25 

   26 (1 row(s) affected)

   27 */

That was pretty much simple. You will need a slightly modified version of this query, if you need to read the values from an XML column. We need to use CROSS APPLY so that we can access more than one record unlike the previous example, where we had only one variable.

    1 /*

    2 Let us create a table and insert an XML value.

    3 */

    4 CREATE TABLE Employees ( Emp XML )

    5 INSERT INTO Employees ( Emp )

    6 SELECT

    7 '<Name>

    8     <First>Jacob</First>

    9     <Last>Sebastian</Last>

   10 </Name>'

   11 

   12 /*

   13 Let us try to query the table now

   14 */

   15 SELECT

   16     n.l.value('First[1]','VARCHAR(20)') AS First,

   17     n.l.value('Last[1]','VARCHAR(20)') AS Last

   18 FROM Employees

   19 CROSS APPLY Emp.nodes('//Name') n(l)

   20 

   21 /*

   22 OUTPUT:

   23 

   24 First                Last

   25 -------------------- --------------------

   26 Jacob                Sebastian

   27 

   28 (1 row(s) affected)

   29 */

My XML Workshop V at SQLServerCentral presents a detailed tutorial which explains the usage of CROSS APPLY. It also presents a few different ways to query a table and retrieve different pieces of information.



After the steps of webhosting and domain name registration, internet marketing takes over. The cheap hosting site can thus spend on backup software as well.This can only be done after estimating the web space. Programs like adwords advertising can easily be employed later.

0 comments:

Post a Comment

About Me
Jacob Sebastian
Tutorials
* XQuery Tutorials
My Links
SQL Server White Papers
SQL Server 2008
My Articles
XML Workshop RSS Feed
Contact me
Readers
Free Hit Counter
Web Site Hit Counters
SQL Server Bloggers
Blog Directories
blogarama - the blog directory Programming Blogs - BlogCatalog Blog Directory
 
Copyright Jacob Sebastian