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

**URL:** <https://boards.straightdope.com/t/can-i-make-an-xml-call-from-a-sql-stored-procedure/538415>\
**Category:** Factual Questions\
**Created:** [May 5, 2010, 3:58pm UTC](https://boards.straightdope.com/t/can-i-make-an-xml-call-from-a-sql-stored-procedure/538415 "2010-05-05T15:58:56Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![tdn](https://avatars.discourse-cdn.com/v4/letter/t/94ad74/32.png) [@tdn](https://boards.straightdope.com/u/tdn)\
**Post date:** [May 5, 2010, 3:58pm UTC](https://boards.straightdope.com/t/can-i-make-an-xml-call-from-a-sql-stored-procedure/538415/1 "2010-05-05T15:58:56Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![bup](https://avatars.discourse-cdn.com/v4/letter/b/6bbea6/32.png) [@bup](https://boards.straightdope.com/u/bup)\
**Post date:** [May 5, 2010, 6:23pm UTC](https://boards.straightdope.com/t/can-i-make-an-xml-call-from-a-sql-stored-procedure/538415/2 "2010-05-05T18:23:55Z")

</div>

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

[http://www.sommarskog.se/share\_data.html](http://www.sommarskog.se/share_data.html)

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

---

<div class="post-metadata">

**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\
**Post date:** [May 5, 2010, 6:28pm UTC](https://boards.straightdope.com/t/can-i-make-an-xml-call-from-a-sql-stored-procedure/538415/3 "2010-05-05T18:28:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\
**Post date:** [May 5, 2010, 6:35pm UTC](https://boards.straightdope.com/t/can-i-make-an-xml-call-from-a-sql-stored-procedure/538415/4 "2010-05-05T18:35:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![tdn](https://avatars.discourse-cdn.com/v4/letter/t/94ad74/32.png) [@tdn](https://boards.straightdope.com/u/tdn)\
**Post date:** [May 5, 2010, 6:59pm UTC](https://boards.straightdope.com/t/can-i-make-an-xml-call-from-a-sql-stored-procedure/538415/5 "2010-05-05T18:59:29Z")

</div>

> [@chrisk](#):
>
> Let me know if this is helpful.

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.