Can I make an XML call from a SQL stored procedure?

Factual Questions
1

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

SELECT
cust_id,
fname,
lname,
xmlcall(customerbase( + cust_id + ).demographics.personal.age
FROM
MyTable

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.

Is this even possible?

2

I’ve only gone the other direction, but does this article help?

http://www.sommarskog.se/share_data.html

(search on that page for the phrase ‘To retrieve the titles from the XML document’)

3

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.

4

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.

Let me know if this is helpful.

5

Yes, actually, I stumbled accross it a couple of hours ago and I’m now playing with it. I think that that’s the route I need to go.