W3C as a query language designed to programmatically interact with and manipulate Extensible Markup Language (XML). XQuery uses XPath methods and expressions to search and retrieve XML data. In this article, I’d like to show a few simple, practical examples of these implementations. First, let’s create a table to hold sample XML data.
CREATE TABLE Actors (ID INT IDENTITY, ContactInfo XML) GONow let’s insert a record containing some XML code.
INSERT INTO Actors VALUES(' <Contact> <Names> <Name type="Legal"> <First>Thomas</First> <Middle>Cruise</Middle> <Last>Mapother</Last> </Name> <Name type="Stage"> <First>Tom</First> <Middle></Middle> <Last>Cruise</Last> </Name> </Names> <Addresses> <Address type="Primary"> <Street>12345 Main Street</Street> <City>San Diego</City> <State>CA</State> <Zip>92130</Zip> </Address> <Address type="Other"> <Street>6200 Cruise Avenue</Street> <City>San Fernando</City> <State>CA</State> <Zip>92126</Zip> </Address> </Addresses> <Phones> <Phone type="Mobile">8085554422</Phone> <Phone type="Home">8085553399</Phone> </Phones> </Contact> ') GOLet’s add another record:
INSERT INTO Actors VALUES(' <Contact> <Names> <Name type="Legal"> <First>Nicole</First> <Middle>Mary</Middle> <Last>Kidman</Last> </Name> </Names> <Addresses> <Address type="Primary"> <Street>1001 Oak Avenue</Street> <City>San Diego</City> <State>CA</State> <Zip>92130</Zip> </Address> <Address type="Other"> <Street>555 Main Street</Street> <City>San Fernando</City> <State>CA</State> <Zip>92126</Zip> </Address> </Addresses> <Phones> <Phone type="Mobile">8085554400</Phone> <Phone type="Home">8085553300</Phone> </Phones> </Contact> ') GOA quick SELECT * FROM Actors now yields the following results:
Value() Method
The first method we’ll look at is the value() method. This method requires two parameters: an XQuery string, and a data type to deposit the results of the query into. Let’s try an example:SELECT ContactInfo.value('(/Contact/Names/Name)[1]', 'varchar(50)') AS Name FROM Actors GOThis statement utilizes the value() method, specifying that the values in the first ‘Name’ node of every ‘Names’ group of nodes in each ‘Contact’ node be retrieved. The ‘[1]’ is required to explicitly request that a singleton be returned (a scalar value). The results are:
SELECT ContactInfo.value('(/Contact/Names/Name)[2]', 'varchar(50)') AS Name FROM Actors GOThe results are:
SELECT ContactInfo.value('(/Contact/Names/Name/First)[1]', 'varchar(50)') AS FirstName, ContactInfo.value('(/Contact/Names/Name/Middle)[1]', 'varchar(50)') AS MiddleName, ContactInfo.value('(/Contact/Names/Name/Last)[1]', 'varchar(50)') AS LastName FROM Actors GOWhich returns:
Query() Method
What if we want to pick which name we retreive, based on the type? This is where the query() method can be used. The query() method requires only one parameter, an XQuery string. Let’s run the following statement to retreive all of Tom’s last names by type :SELECT ContactInfo.query('/Contact/Names/Name[@type="Legal"]/Last') AS LegalLastName, ContactInfo.query('/Contact/Names/Name[@type="Stage"]/Last') AS StageLastName FROM Actors WHERE ID = 1 GOThis time, we focus on the element attribute of ‘type,’ rather than child elements. The query() method takes search parameters, beginning with ‘@’, followed by the name of the element attribute. Running the above statement returns the following results (note that we specified ID = 1, which says that we only want to look at the ContactInfo field associated with Tom, not Nicole):
Text() Method
You’ll also notice that the query() method returns values in the form of XML nodes. To parse out the actual data, and convert the results to a string , we can run the following query:SELECT CONVERT(VARCHAR(50),ContactInfo.query('/Contact/Names/Name[@type="Legal"]/Last/text()')) AS LegalLastName, CONVERT(VARCHAR(50),ContactInfo.query('/Contact/Names/Name[@type="Stage"]/Last/text()')) AS StageLastName FROM Actors WHERE ID = 1 GO