Dynamic column names in XML
-
I am experimenting with XML and I would like to pass a column name as an argument of a procedure or function. The following code creates a simple XML variable and then reads from it.
The first SELECT statement succeeds as column name 'Color' is explicitly named. The second SELECT statement does not return the expected result (and is the best I could achieve without getting syntax errors!). I want both SELECT statements to return the same results, the second statement being dynamic. Is that possible?
DECLARE @T TABLE (ID INT, Color VARCHAR(10), Size VARCHAR(10))
INSERT INTO @T SELECT 1, 'Blue', 'Small'
INSERT INTO @T SELECT 2, 'Red', 'Medium'
INSERT INTO @T SELECT 3, 'White', 'Large'
DECLARE @XML XML = (SELECT * FROM @T FOR XML RAW)
SELECT
X.Y.value('@ID', 'INT') AS ID,
X.Y.value('@Color', 'VARCHAR(10)') AS Value
FROM @XML.nodes('/row') AS X(Y)
DECLARE @ColumnName VARCHAR(10) = 'Color'
SELECT
X.Y.value('@ID', 'INT') AS ID,
X.Y.value('sql:variable("@ColumnName")', 'VARCHAR(10)') AS Value
FROM @XML.nodes('/row') AS X(Y)- This topic was modified 3 years, 12 months ago by cmartel 20772.
- This topic was modified 3 years, 12 months ago by cmartel 20772.
-
Try this
SELECT
X.Y.value('@ID', 'INT') AS ID,
X.Y.value('./@*[local-name()=sql:variable("@ColumnName")][1]', 'VARCHAR(10)') AS Value
FROM @XML.nodes('/row') AS X(Y)____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks a lot. I will preciously keep a copy of this code!
-
You missed one of the most fundamental, concept of RDBMS. A column models a particular attribute inside of the declaration of a particular set of entities. They do not magically change from weight to color, but the attributes, nor did the tables move from automobiles to elephants. Apparently not knowing even the most fundamental concepts of RDBMS have led you to use XML for something it was never meant to be used for, namely some sort of weird dynamic SQL. Then the SQL you wrote is a mess.
1) Since the table models a set of entities, it should be a collective or plural name. You might want to read the ISO 11179 naming rules or the metadata committee standards. But when you're still a BASIC programmer, you don't think about meaningful names.
2) by definition, not as an option, a table must have a key. A key by definition cannot be NULL-able.
3) there is no such thing in RDBMS is a generic, universal, Kabbalah "_id" because an identifier is on a nominal scale and by definition it is never used for computations. That means it can't be a numeric! It also has to be the identifier of something in particular, thanks to something called the law of identity from logic.
4) the correct way to name a column is <attribute>_<attribute property>. Again, see the ISO standards.
5) it is simply good programming to use check () constraints instead of trying to do this in the invoking programs.
CREATE TABLE Foobars
(foo_id CHAR(10) NOT NULL PRIMARY KEY,
color_name VARCHAR(10) NOT NULL
CHECK(color IN ('red', 'white', 'blue'),
size_name VARCHAR(10) NOT NULL
CHECK (size_name IN ('small', 'medium' , 'large'))
);
You're also a few decades behind on the syntax for insertion statement. You're not anywhere near the current syntax, but still want to use the old Sybase stuff from about 50 years ago. Why? This one row per statement insertion cannot be optimized.
INSERT INTO Foobars
VALUES
('0000000001', 'Blue', 'Small'),
('0000000002', 'Red', 'Medium'),
('0000000003', 'White', 'Large');
I've wanted to write a 12th book on bad SQL and misconceptions. This is so typical that you'd probably be in the first two or three chapters.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
Wow, Joe!!! Condescending much??
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Hello Mike,
As knowledgeable Joe might be, he seems to ignore some of the challenges programmers are facing each and every day.
I can certainly write better code than the script I posted. I just wanted it to be very simple in order to emphasis the problem I was trying to solve. Moreover, I know that what I am trying to solve reflects very bad database design but this is the database I am being paid to work on and just saying «this is bad design» is not an option.
User-defined columns (names are stored in a table) were added over the years (and are still added) and I need a way to retrieve every row where a given column has a given value. Having parameters @column and @value, one can easily write something like sp_executesql SELECT ID FROM TABLE WHERE @column = @value. Another option is to write a very fast CLR function that receives @column and @value as arguments. I can also write a huge table-value function that contains a case for every user-defined variable, function that will have to be altered whenever a new user-defined column will be created.
I know that the way user-defined values were implemented is far from being optimal and that it will ultimately have to be refactored. The XML solution mentioned in this discussion is not really meant to be implemented but is nevertheless interesting since it shows how a dynamic statement can be executed from within a function (where sp_executesql cannot).
Learning what not to do is also learning.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply