February 20, 2008 at 9:11 am
I have a requirement for my application that I allow users to define new fields on the fly. What this mean is this: lets say my app tracks information about cars, so I have a relational Cars table that contains 'Make' & 'Model'. Now lets say 1 particular user also wants to collect 'Color' and 'Year'. Well I was hoping to put that additional data into xml somehow (either as attributes to 1 node or child nodes with values or something), and now I'm trying to write a query that will join those 4 items together into 1 table with 4 columns. I would like to write this in such a way so that I can use this table in other queries (which eliminates stored procedures which happened to be the only way I have thus far been able to solve my dilemma).
Thanks!
Steve
February 20, 2008 at 9:31 am
Well - without any specifics, it's going to be tough to know if this is applicable. However - assuming your table had an XML column called X, and X contained XML formatted as such:
<attribs>
<color> Red </color>
<year> 1999</year>
</attribs>
Then your query could be simply:
select
Make,
Model
x.value('(/attribs/color)[1]','varchar(20)') as Color
x.value('(/attribs/year)[1]','int') as Year
from Mytable
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 20, 2008 at 9:42 am
Matt Miller (2/20/2008)
Well - without any specifics, it's going to be tough to know if this is applicable. However - assuming your table had an XML column called X, and X contained XML formatted as such:
<attribs>
<color> Red </color>
<year> 1999</year>
</attribs>
Then your query could be simply:
select
Make,
Model
x.value('(/attribs/color)[1]','varchar(20)') as Color
x.value('(/attribs/year)[1]','int') as Year
from Mytable
Thanks for the quick reply. Is there a way for me to write something like:
select
Make,
Model
x.value('(/attribs/*)[1]','varchar(20)') as Color
from Mytable
I do have knowledge of all the items in the attribs xml, but I'd like to be able to write my query without that knowledge or looking in another table for that knowledge. Ideally I'd like to create a view or udf that could return the table with all the columns and be able to use that same view for 2 different customers that have different xml.
February 20, 2008 at 10:19 am
By the way - there are commas missing in my original query (that's going to confuse the old parser).
That should actually be:
select
Make,
Model,
x.value('(/attribs/color)[1]','varchar(20)') as Color,
x.value('(/attribs/year)[1]','int') as Year
from Mytable
Not sure that there's a straightforward way to do that. If you were to use:
select x.*,
k.value('local-name(.)','varchar(20)') as XMLColumnName
k.value('.','varchar(20)') as XMLColumnValue
from x cross apply x.i.nodes('/attribs/*') j(k)
but then you would need to pivot the XMLcolumnName and value in order to do what you wish. that's dynamic SQL land, in which case you'd be better off just dynamically writing out the first syntax.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 20, 2008 at 10:33 am
Thanks again for the quick reply! I appreciate your help I've been beating my head against the wall for a while trying to figure out an acceptable solution for this.
the dynamic sql route will actually work for a portion of my needs. I have code that can generate the dynamic sql to return a resultset that I can bind to a grid and I will be able to sort on the dynamic sql as the sort order of the grid changes. And this works great.
The other problem is reports. I want to be able to generate reports as well. I was hoping to be able to write a generic something (query, view, table...not sure) that the report to look at and list all the items associated with a car. I guess if the report knows what columns to display then I can report that way. I don't know much about how the reports work, I just believed that I needed to create an easy access table to get at this information.
I originally went down the path of PIVOT table. I was storing these custom values in a table instead of in xml with the row. I was unable to find a solution using the pivot either due to the dynamic nature of my column list. I was able to fix that problem with an exec command and a dynamic sql statement, but this won't work in a view or udf or to be easily joined with other tables.
XML seems like the way to go, and everything I've read say that what i'm trying to do it one of the main uses for xml within sql server, but I can't find an example of anyone actually doing it.
Thanks again!
STeve
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply