Dimitrij.P

XQuery of XML label attribute

10 posts in this topic

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

 

Share this post


Link to post
Share on other sites

I'm not sure of the environment you're working in, but I bet that the index 1 gets the node value. You're looking for one of the node attributes, so you probably need to replace that index 1 with whatever is necessary to get the "label" attribute.

Share this post


Link to post
Share on other sites

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 :D

 

Thank you again!!!

When I found the right solution I will post it here.

 

Regards

Dimitrij

Share this post


Link to post
Share on other sites

@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

 

I'm not sure of the environment you're working in,..

 

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

Edited by Dimitrij.P

Share this post


Link to post
Share on other sites

Where is this kind of code:

 

t.xmlc.value('(metadata/fields/field[@name="cusInt05"]/option[@value=sql:column("items.cus_int_05")])[1]', 'varchar(50)')
That's not part of the SQL query, right?

Share this post


Link to post
Share on other sites

Where is this kind of code:

 

t.xmlc.value('(metadata/fields/field[@name="cusInt05"]/option[@value=sql:column("items.cus_int_05")])[1]', 'varchar(50)')
That's not part of the SQL query, right?

 

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

Edited by Dimitrij.P

Share this post


Link to post
Share on other sites

I would check the documentation to figure out how to access the attribute instead of the node value. How did you figure out how to write the XML query for the node value, did that come from documentation somewhere?

Share this post


Link to post
Share on other sites

How did you figure out how to write the XML query for the node value, did that come from documentation somewhere?

 

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?

Share this post


Link to post
Share on other sites

I don't, I'm not familiar with most of what you're using.

Share this post


Link to post
Share on other sites

I don't, I'm not familiar with most of what you're using.

Me neither :rofl::rofl::rofl: But I'm trying.

Thank you for your help so far. When I find the solution I will post it here.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now