Home > Articles > Data > SQL Server

  • Print
  • + Share This
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


  • + Share This
  • 🔖 Save To Your Account