Oh, I'm sorry. I should clearify it more in detaild at the beginning.
This code is a part of a SQL Query.
I have an SQL DB in which severalt tables are stored. In one table I have a conten which I want to link to EXCEL Pivot. Some of the values of this table are ID's which point to another tables.
For qerying SQL table to SQL table with usual content I don't had any troubles.
But one of the items in one of the tables is this XML code. And some of the values in the table I want to link are declared in this XML code.
The code has this structure:
<metadata>
<field name=""cusInt05"" label=""Name"">
<option value=""1"">John</option>
<option value=""2"">Moritz</option>
<option value=""3"">Britney</option>
…
</field>
…
<states>
<state status=""0"" label=""single""/>
<state status=""1"" label=""married""/>
<state status=""2"" label=""divorced""/>
…
</states>
</metadata>
And here is my SQL query:
SELECT
items.id AS 'number' -- column with the number ID
, items.time_stamp AS 'birthday' --column with the birthday timestamp
, items.status AS 'status' --column with the status values 1,2,3,... which are declared in the XML "states" and which I want to get returned
, t.xmlc.value('(metadata/fields/field[@name=cusInt05]/option[@value=sql:column("items.cus_int_05")])[1]', 'varchar(50)') AS 'Team' -- column where I already get the values of XML declaration returner depending on the values out of column "items.cus_int_o5"
FROM
jtracHA.dbo.items items
, (select CAST(replace(replace(replace(replace(replace(replace(replace(CAST(xml_string AS varchar(MAX)), 'Ä', 'A'), 'ä', 'a'), 'ë', 'e'), 'ï', 'i'), 'ö', 'o'), 'Ü', 'U'), 'ß', 'ss') AS xml) AS xmlc from jtracHA.dbo.metadata where id=2) t
Since I'm a newbie I'm happy to get this progress. And now by your hint I understand that the value methode is OK for the XML "fields" where the nodes realy have values. But the nodes "states" don't have any values just attributes as you mentioned.
I hope it could clarify the working enviroment.
Regards
Dimitrij