Re: Extracting XML data to columns during query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Apr 3, 11:17 pm, Kent Tegels <kteg...@xxxxxxxxxxx> wrote:
Hello Brian,

I'm working with an inherited SQL 2000 database (now moved to 2005)
that stores strings of XML in a text column. I'd like to avoid
changing the schema right now. Each row's XML data looks something
like:
The exact attributes in the inner element are unknown, but what I
would like to be able to do is return them as columns in a query such
that I would get:

Theres lots of ways of doing this is the number of attributes are know, but
when they aren't, your going to have a hard time shaping them to a meaningful
table. So whate exactly do you mean by "attributes are unknown?"

Thanks!
Kent Tegels
DevelopMentorhttp://staff.develop.com/ktegels/

Well, the xml data holds responses to online forms. Each form may have
different fields and each form field results in a single attribute key/
value pair. If a form has a first name and last name, the attributes
for these two would show up in the XML.

Obviously this makes it a bit more difficult than if the values were
known. However, for this, we can assume that any given set of data
pulled will be for one particular form, and will thus have the same
attributes in the XML data. I may just have to do this on the client
end, but thought I'd see if a SQL method was available.

.