extract XML values from multilanguage xml string storage

Question

Post reply

extract XML values from multilanguage xml string storage

blom0344

SSCrazy

Points: 2045
More actions
May 18, 2009 at 2:18 am

#221377

Hi,
I have a number of descriptive tables that store multi-language XML strings like:
"Somevalue1somevalue2"
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..

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 1

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

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 2

Should have added

There is a lot of material here:

http://msdn.microsoft.com/en-us/library/ms345117.aspx

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 3

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))

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 4

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/61537

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 5

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

blom0344 SSCrazy Points: 2045 More actions · Answer 6

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?

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 7

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/61537

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 8

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

blom0344 SSCrazy Points: 2045 More actions · Answer 9

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

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 10

Does that even work for you?

It's not returning anything when I run it on my test table

blom0344 SSCrazy Points: 2045 More actions · Answer 11

It does exactly what I want ,when I specify the right languagecode.

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 12

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.

blom0344 SSCrazy Points: 2045 More actions · Answer 13

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..

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 14

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