- Creating the Database Structure
- Inserting the Data?
- Retrieving the Data?
- Querying the Database?
- Updating the Records?
- Summary?
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Querying the Database
There is a simple short answer to the question "How do I query the XML values I have stored in my database?" Don’t even think about it.
If your queries consistently refer to a value that is stored in the XML markup, you should store that value as a separate table column. This will also enable you to build an index on that value, further speeding up the data retrieval.
However, if you need an occasional check on the presence of a value (for example, find all products that have color specified), you could cheat a bit with the LIKE operator, finding all products where the XML string includes the desired start tag (see Listing 10).
Listing 10 Find all products with known color
SELECT * FROM Products WHERE XMLData LIKE ’%<Color>%’
Similarly, to find all red products, you could write a slightly longer LIKE pattern, as shown in Listing 11. (And again, if you make queries like this, the product color property should really be an indexed table column.)
Listing 11 Find all red products
SELECT * FROM Products WHERE XMLData LIKE ’%<Color>Red</Color>%’
Both MySQL (starting with version 5.1.5) and SQL server (starting with SQL Server 2000) provide XML-extracting functions that can be used in SQL queries to avoid the kludges like the LIKE operator.
For example, you’d use the SQL query in MySQL shown in Listing 12 to find products with red color.
Listing 12 Find all red products using a MySQL XML function
SELECT * FROM Products WHERE ExtractValue(XMLData,’//color’) = ’Red’
However, even though these functions are implemented in the database server, they are still a resource hog—every XML string has to be parsed into the DOM tree (on the database server) and the desired value extracted with the XPATH query.