May 18, 2009 at 2:18 am
Hi,
I have a number of descriptive tables that store multi-language XML strings like:
"
to extract the string "Somevalue1", I use an expression like:
SUBSTRING(PP.Name, CHARINDEX('1033', PP.Name) + 6, CHARINDEX('', PP.Name)-6-CHARINDEX('1033', PP.Name)) as PF_Name
which is doing alright when 1033 is positioned before 1043. However, I want to be sure I can rely on a mechanism that can cope with more languages (or another order in which they are stored)
Love to get some input for this one..
May 18, 2009 at 3:55 am
I've not done much with XML so thought I would have a play and came up with this:
declare @xml as xml
set @xml = '
Somevalue1
somevalue2
somevalue3
'
select @xml
SELECT nref.value('text[1]', 'varchar(500)') [text]
FROM @xml.nodes('//translation') AS R(nref)
WHERE nref.exist('.[@language = "1023"]') = 1
declare @language as varchar(4)
set @language = '1023'
declare @sql as varchar(5000)
set @sql =
'declare @xml as xml
set @xml = ''
Somevalue1
somevalue2
somevalue3
''
SELECT nref.value(''text[1]'', ''varchar(500)'') [text]
FROM @xml.nodes(''//translation'') AS R(nref)
WHERE nref.exist(''.[@language = "'+@language +'"]'') = 1'
print @sql
exec (@SQL)
The ONLY way I could get it to work though was to wrap the "somevalue" in another XML tag, in this case
May 18, 2009 at 3:55 am
Should have added
There is a lot of material here:
May 18, 2009 at 4:04 am
Posted too quick (and I hate not getting something working!!)
declare @xml as xml
set @xml = '
Somevalue1
somevalue2
somevalue3
'
declare @language as int
set @language = 1023
SELECT R.nref.value('.[1]', 'varchar(500)') [text]
FROM @xml.nodes('/translations/translation') AS R(nref)
WHERE R.nref.value('@language', 'varchar(500)') = cast(@language as varchar(4))
May 18, 2009 at 4:11 am
No need for dynamic SQL
DECLARE @language VARCHAR(4)
SET @language = '1023'
SELECT r.value('.','varchar(100)') AS Value
FROM @xml.nodes('/TRANSLATIONS/TRANSLATION[@language=sql:variable("@language")]/TEXT') AS x(r)
Also the XML isn't valid, the attributes are missing double quotes
____________________________________________________
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/61537May 18, 2009 at 4:19 am
Mark (5/18/2009)
No need for dynamic SQL
agreed:-)
I got it working without the dynamic SQL 2 minutes later and corrected my post 😀
I can't get your version working though... doesn't return anything
May 18, 2009 at 6:54 am
Ahum,
The xml expression itself is stored as a nvarchar(max) type and not as an xml datatype. I guess that means that the xml type of query expressions will not work?
May 18, 2009 at 6:57 am
Try this, no extra TEXT elements
declare @xml as xml
set @xml = '
Somevalue1
somevalue2
somevalue3
'
DECLARE @language VARCHAR(4)
SET @language = '1023'
SELECT r.value('.','varchar(100)') AS Value
FROM @xml.nodes('/TRANSLATIONS/TRANSLATION[@language=sql:variable("@language")]') AS x(r)
Double quotes around attributes seem to get removed inside "Code" tags
____________________________________________________
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/61537May 18, 2009 at 7:11 am
blom0344 (5/18/2009)
Ahum,The xml expression itself is stored as a nvarchar(max) type and not as an xml datatype. I guess that means that the xml type of query expressions will not work?
you can cast it as an XML data type
select *
into #temp1
from (
select '
Somevalue1
somevalue2
somevalue3
' xmlcol
union all
select '
Someothervalue1
someothervalue2
someothervalue3
' xmlcol)t1
declare @language as int
set @language = 1033
select
(SELECT R.nref.value('.[1]', 'varchar(500)') [text]
FROM xmlcol.nodes('/translations/translation') AS R(nref)
WHERE R.nref.value('@language', 'varchar(500)') = cast(@language as varchar(4))) as col1
from (select cast(xmlcol as xml) as xmlcol from #temp1 ) T1
drop table #temp1
May 18, 2009 at 8:48 am
The following is what I got at the moment:
select temp.id,temp.xmlname.value('(//Translations/Translation[@Language = "1043"])[1]','nvarchar(max)') from
(select id, cast(name as xml) as xmlname from someschema.sometable) temp
Still do not understand why the [1] is needed though
May 18, 2009 at 8:53 am
Does that even work for you?
It's not returning anything when I run it on my test table
May 18, 2009 at 1:15 pm
It does exactly what I want ,when I specify the right languagecode.
May 19, 2009 at 1:33 am
blom0344 (5/18/2009)
It does exactly what I want ,when I specify the right languagecode.
Bizarre - its not doing anything for me 😀
To answer your other question the [1] tells the XML parser which of the potential XML nodes to return in the case of there being several potential candidates, you want the first one.
May 19, 2009 at 2:44 am
That is a bit scary, cause I want to make sure that it will work over at customers.
Would it be too much to ask to try my table download in the attachment?
Much obliged..
May 19, 2009 at 3:48 am
What you have is fine...
Where the [1] comes to play is, suppose you have the following XML:
Residential
Woning
Home Based
when you match on Language = "1033" there are 2 possible Translation tags relating to "Residential" and "Home Based"
The [1] tells the XML parser to take "Residential" as this is the first one it encounters
If you changed the [1] to [2] it will use "Home Based"
What you need to ensure is that each language code is unique within each XML block
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply