␡
- The OPENXML Function in Transact-SQL
- Joining OPENXML Output with Other Tables
- Advantages of Using OPENXML
- Performance Issues With OPENXML
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Joining OPENXML Output with Other Tables
A common misunderstanding is that you can't join the output of OPENXML with a table or query. In fact it's a fairly simple concept. Because OPENXML converts the XML into a set of rows and columns, it can be used just like any other table. We can easily modify the previous example to join the output of OPENXML to the titles table. I won't repeat the XML structure to save space:
DECLARE @xml_text VARCHAR(4000), @i INT
/* put xml structure here */
EXEC sp_xml_preparedocument @i OUTPUT, @xml_text
SELECT au_id AS author_id,
au_lname AS last_name,
au_fname AS first_name,
a.title_id,
title,
royaltyper AS royalty
FROM
OPENXML(@i, '/root/authors/titles', 1)
WITH (
au_id VARCHAR(11),
au_lname VARCHAR(20) '../@au_lname',
au_fname VARCHAR(30) '../@au_fname',
title_id VARCHAR(15),
royaltyper INT) a
INNER JOIN titles b ON a.title_id = b.title_id
EXEC sp_xml_removedocument @i
Results:
|
author_id |
last_name |
first_name |
title_id |
Title |
royalty |
|
172-32-1176 |
White |
Johnson |
PS3333 |
Prolonged Data Deprivation: Four Case Studies |
100 |
|
213-46-8915 |
Green |
Marjorie |
BU1032 |
The Busy Executive's Database Guide |
40 |
|
213-46-8915 |
Green |
Marjorie |
BU2075 |
You Can Combat Computer Stress! |
100 |