July 23, 2008 at 4:43 am
Hi,
I'm looking to use the XML data type for storage of language values something like
I have the following query
SELECT Localisation.query('data(/OPTIONLISTS/LISTNAME[@CULTURE="es-ES"])')
FROM dbo.Asset_Lists
Which will return all the rows from my table with the correct culture. However I have a couple of problems
1. How do I pass the culture as a variable ?
I have read that this should be dropped into the where clause something like the following
DECLARE @VARCULTURE NVARCHAR(5)
SET@VARCULTURE = 'es-ES'
SELECT Localisation.query('data(/OPTIONLISTS/LISTNAME)')
FROM dbo.Asset_Lists
WHERE Localisation.exist('/OPTIONLISTS/LISTNAME[@CULTURE=sql:variable("@VARCULTURE")]') = 1
Would expect to return 'EL COCHE FABRICA' but returns all the values within the optionlists i.e. 'CAR MANUFACTURES','EL COCHE FABRICA'
2. Is there away to default the return value to en-GB in the event of a null value being returned ?
In the above the above example if a culture is passed through of 'fr-FR' no result will be returned but in this instance I would like it to return the en-GB value.
I have tried using the ISNULL function but the value doesn't seem to be recorded as a null.
Any ideas would be greatly appreciated.
Thanks,
James.
July 23, 2008 at 5:23 am
[font="Courier New"]DECLARE @VARCULTURE NVARCHAR(5)
SET @VARCULTURE = 'es-ES'
DECLARE @val NVARCHAR(100)
SET @val = 'data(/OPTIONLISTS/LISTNAME[@CULTURE="' + @VARCULTURE + '"])'
SELECT Localisation.query(@Val)
FROM dbo.Asset_Lists[/font]
On a side note, this is a pretty big mis-use of the XML data type. You have essentially turned your possibly relational database into a big set of BLOB fields that are going to be pretty much unindexed. I would expect your solution to be fine with a small data set, but you are going to have major scalability issues by storing everything in XML fields.
If you are designing from the ground up, I would suggest you go to a relational solution that uses a language key and keeps the individual language values in their own records. It will be far more searchable, probably take less disk space, and end up performing much better.
July 23, 2008 at 5:53 am
Thanks for the Code.
We are starting a new design so at the moment nothing is fixed.
Understand your point of view as this does not fit into the relational model, but we where after a way to perform list management in a simple way that allowed us to create new lists, but to also support different language options for each data element for the list and list items i.e Colours, Vehicle Makes, Vehicle Models etc.
so rather than have a single table per list (currently expecting 15 lists) and then having a separate column per language (we are initially having to support 20 languages) we thought about using the XML data type. This means creating two tables OptionLists and OptionListItems using the XML type for holding the value name(this is a pure display attribute as we are still looking to use id's to maintain PK's and FK's, and from the application they will be using the fixed id)
I have looked around the net for other solutions that meet these core requirements of language expansion, and list creation but have not found anything else.
Any other suggestions of performing this task would be greatly appreciated!
July 23, 2008 at 6:12 am
Sorry forgot to say using sql server 2005, but you probably already got that.
Tried running the code but get the following error:
The argument 1 of the xml data type method "query" must be a string literal
Thanks
July 23, 2008 at 6:27 am
Sorry, I forgot about the literal issue. I try to avoide using the XML data types.
[font="Courier New"]DECLARE @VARCULTURE NVARCHAR(5)
SET @VARCULTURE = 'es-ES'
DECLARE @sql NVARCHAR(100)
SET @sql = 'SELECT Localisation.query(''data(/OPTIONLISTS/LISTNAME[@CULTURE="' + @VARCULTURE + '"])'') FROM dbo.Asset_Lists'
EXEC sp_ExecuteSQL @sql[/font]
I would suggest a table that looks like:
[font="Courier New"]RecID ValueID LanguageID Value
1 1 1 CAR MANUFACTURES
2 1 2 EL COCHE FABRICA[/font]
This would give you a single table that holds all of your languages and supports easy querying and indexing. Your "foreign keys" would be based on the "ValueID". This would require managing referential integrity using check constraints and possibly triggers, but would probably be far more maintainable and scalable.
July 23, 2008 at 6:31 am
It would also give you a really easy ability to create language-specific views or even user-specific language views.
If you have a table with a user name and a primary language ID, you could easily create a set of views that showed all of your data in their language
[font="Courier New"]CREATE VIEW UI.List
AS
SELECT L.* FROM Base.List L INNER JOIN Base.UserLanguage UL ON UL.LanguageID = L.LanguageID
WHERE UL.UserName = SUSER_SNAME()[/font]
That may make programming your UI much easier.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply