XML Workshop V – Reading Values from XML Columns
Posted on November 5th, 2007 | by Admin |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.
Sorry, comments for this entry are closed at this time.