I swear I’ve seen this done before, but I can’t find information on it. I’m looking in SQL Help and seeing examples of OPENXML and writing data in XML format, but these aren’t what I want. What I want is something like
This is assuming that MyTable has a lot of data that I need, but for some reason it doesn’t store age, but another database (accessed only through an XML call) does.
I think that what you want here might be a SQL User-defined function aka UDF, not a stored procedure - you pass the cust_id to the UDF, use openxml inside it, and return the proper age.
UDFs can be expensive compared to procedures, (because you call them once for every row in the query, in a case like this,) but they do give you a lot of flexibility.
Importing the XML into SQL server is another possibility of course.
Update. After looking at openxml, I don’t think you’d need a user-defined function. Try instead:
SELECT
mt.cust_id,
mt.fname,
mt.lname,
x.age xmlcall(customerbase( + cust_id + ).demographics.personal.age
FROM
MyTable mt join openXML (@whatever, ‘/this/that’, 1) x
on mt.cust_id = x.cust_id
You’d need to experiment with the right way to call openxml to get the data you want out of it the right way, but the idea is that openXML stands in for a table, so you can join to it from myTable the same way you would join to a native table.