Jump to content

Dimitrij.P

Members
  • Posts

    6
  • Joined

  • Last visited

Dimitrij.P's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. Me neither But I'm trying. Thank you for your help so far. When I find the solution I will post it here.
  2. Hi, I hunted for it. In web there are several examples of how to query the VALUe of XML nodes. Till now I couldn't find any examples of how to query ATTRIBUTES. Suddenly I don't have any documentation and the one I found for SHQLDB contents only a hint for VALUES. That's why I asked for it here, maybe somebody already had this issue. Do you know where I can finde this kind of documentation?
  3. 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
  4. @all for sure if somebody has an idea or I can provide more information here to help you understand I'll appreciate your help. @justsomeguy how can I give you a hint of the working enviroment? The only things I know exactly are: the DB is based on HSQLDB the XML item within the DB from where I want to have the content is "xml version="1.0" encoding="UTF-8" Is it the right information or am I on the wrong path? Regards Dimitrij
  5. Hi justsomeguy, thank you very much for this hint!!! It is very useful for me to know that the index 1 is for the node value. Now I can search for the whatever it is to get the attribute Thank you again!!! When I found the right solution I will post it here. Regards Dimitrij
  6. Hi to all experts, I'm Dimitrij. I' a newbie in the topics of XML, SQL, query but I started to work with this topics some weeks ago and I like it. Now I have a question and hope to get your support. I try to explane the situation and please sorry if I make some formal mistakes, I still learn the right terms. OK, I have a SQL DB and want to link some of the tables with PIVOT by quirying. Beside the usual SQL declarations & tables there is an XML code stored as item in one table. In this XML code are vaues which I want to be returned by querying. The values iside of the XML code are defined by a web interface. So I don't have any chance to change the structure of the XML code. After many days of learning, trying, googleing, ... I celebrated some achievements :Happy: . One peace of the XML declaration 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> … </metadata> In the DB table I want to link I have a column ("cus_int_05") with the values 1, 2, 3... In the table I want to produce I want to replace the values with the content of the options of XML code. So I want to use the value from SQL table as parameter in query and get the content of the XML node "cusInt05" depend on the parameter like "John" or "Moritz". For this I created following string which works fine (to save place I don't put the whole query sentence). I use the value() function ad use the sql function sql:column() as a parameter: t.xmlc.value('(metadata/fields/field[@name=cusInt05]/option[@value=sql:column("items.cus_int_05")])[1]', 'varchar(50)') So far I got it on my own. Now I have another peace of the XML declaration where I can configure the state in the web interface It has this structure: <metadata> <states> <state status=""0"" label=""single""/> <state status=""1"" label=""married""/> <state status=""2"" label=""divorced""/> ... </states> </metadata> In the same SQL table but another column I have the values 0, 1, 2,... for status. Again in the table I want to produce I want to replace the values with the content of this node like "single", "married" depend on the parameter. I've tried the same schema but sinthe the content of this node is not value, is something like "label" it doesn't work and I don't know how to handle this. t.xmlc.value('(metadata/states/state[@status=sql:column("items.status")])[1]', 'varchar(50)') I would realy appreciate if you could help me even just by giving a tip. Thank you very much in advance! Regards Dimitrij
×
×
  • Create New...