SQL Query auf XML inhalt eines SQL items

D

Dimitrij.P

Benutzer
Beiträge
9
Hallo an alle Experten,
ich heiße Dimitrij und hoffe auf eure Hilfe.

Die Welt von DB und SQL ist für mich neuland. Dennoch möchte und muss ich mich arbeitsbedingt damit beschäftigen. Ich habe mich bereits etwas in die Basics von SQL und von Querys eingearbeitet und kleine "Erfolge" gefeiert :D

Jetzt stehe ich vor dem folgenden Problem:
Ich habe in einer SQL-DB in der ich erstmal eine Tabelle mit einer Pivot-Tabelle im EXCEL verknüpfe, um Daten auszuwerten. Viele Bezeichnungen sind in dieser Tabelle als ID's hinterlegt, die auf andere Tabellen verweisen.
Mittels Querys konnte ich bereits meine "Wunschtabelle" zusammenstellen und mir fehlt der letzte Schritt zum Ergebniss: in einer der DB-Tabellen sind Bezeichnungen in XML definiert. Ich habe bereist gegoogelt wie verrückt und habe viele Beispiele von Querys SQL-XML gefunden. Der XML Code den ich habe ist etwas komplizierter von der Struktur, sodass ich alleine da leider nicht weiter komme.

Ich versuche es mal zu beschreiben:
1) Die Tabellen befinden sich in der selben DB

2) Hier ist ein reduzierter Beispiel meiner Abfrage - Tabelle mit Namen "items"
DB_Tabellen.JPG
Und mein Abfragecode dazu:
SELECT
items.time_stamp AS 'Erstellungsdatum'
, items.cus_int_04
FROM
jtracHA.dbo.items items
, jtracHA.dbo.metadata metadata
WHERE
--die Basisabfragen habe ich hier für bessere Übersicht weggelassen

3) Die XML-Definition ist in der Tabelle "metadata" Spalte "xml_string":
DB_Tabellen_2.JPG
Und so sieht die Struktur von XML aus:
XML_Code.JPG

Ich möchte jetzt mittels Abfrage die Zahlen in der "items.cus_int_04" durch die Bezeichnungen ersetzen, die in dem XML-Code in "metadata.xml_string" unter Label "Abteilung" definiert sind ersetzten. Sodass ich z.B. statt der "1" "AAA" in der Tabelle habe.

Ich hoffe, dass ich es verständlich erklären konnte und würde mich über eure Hilfe riesig freuen.
Viele Dank im Voraus und viele Grüße

Dimitrij
 
Werbung:
Und natürlich mein erster Versuch, der leider nicht geklappt hat:

SELECT
items.time_stamp AS 'Erstellungsdatum', items.cus_int_04, metadata.xml_string
FROM
jtracHA.dbo.items items, jtracHA.dbo.metadata metadata
WHERE
metadata.xml_string.value('(metadata/fields/cusInt04)', 'varchar(50)') = items.cus_int_04
 
Hallo,
ich verstehe, dass wir Urlaubszeit haben, wäre zumindest für ein Hinweis sehr dankbar. Vielleicht ist es ja falscher Forum/Kategorie oder ich habe es sau schlecht erklärt oder es ist überhaupt kein DB-Thema... Zumindest, was mir weiter helfen könnte...
Danke
 
Hallo akretschmer,
vielen dank für deine Antwort.

Wie bereits geschrieben, bin ich ein Neuling im Thema DB und die DB, die ich habe wurde von einer externen Firma programmiert. Ich habe eine Weboberfläche bekommen, mit der ich dort arbeite und merke, dass die Weboberfläche mir nicht alle Informationen/Funktionen liefert, die ich brauche.
Das einzige was ich weiß ist, dass es sich um SQL handelt. Wie könnte ich die Antwort auf deine Frage am schnellsten herausfinden?

VG
Dimitrij
 
Habe die Platform gefunden, die die externe Firma verwendet hat:

h**p://jtrac.info/doc/html/features.html

Dort steht, dass HSQL verwendet wird, ich hoffe, das war die Antwort.

VG
Dimitrij
 
akretschmer schrieb:
also wohl HSQLDB. Da findet sich auch eine Doku, die solltest Du nun vielleicht mal lesen ... HyperSQL User Guide
Zum Vergrößern anklicken....
Hallo akretschmer,
vielen herzlichen Dank für die Doku. Sie wird mir bestimmt bei meiner weiteren Auseinandersetzung mit dem Thema helfen. Ich habe etwas quer gelesen und gesehen, dass es dort u.a. auch Abfragen erklärt werden und sogar ein Bezug auf XML gegeben wird, mir fehlt noch das Basiswissen, um das zu verstehen.

Für mein jetztiges Problem fehlt mir leider die Zeit, um mich da vernünftig reinzulesen. Vielleicht hast du oder jemand anders einen Lösungsansatz für mich? Wie nahe/fern war ich denn mit meinem Vo.g. Versch bis jetzt?

Danke und viele Grüße
Dimitrij
 
Dein gezeigter Code
Code: 
metadata.xml_string.value('(metadata/fields/cusInt04)', 'varchar(50)')
würde glaube ich in MSSQL so gehen, du scheinst auf einem guten Weg zu sein. Habe selbst etwas mit XML in MSSQL gearbeitet. Leider sieht der SQL-Standard nicht vor das die Syntax oder der Aufbau von XML-Abfragen gleich oder auch nur ähnlich sein muss.

HSQL ist nicht so verbreitet, ich kenne hier leider auch keinen der es nutzt. Daher wirst du wohl mit der Doku einsteigen müssen.

PS: Wenn du einen Beispiel-XML-String als Text hier rein schreibst kann ich dein gezeigtes Query unter MSSQL testen. Vielleicht ist die Syntax richtig, deine Weboberfläche (SQL Client) ermöglicht aber nicht den vollen Funktionsumfang des Servers, kommt oft genug vor.
 
Zuletzt bearbeitet:
ukulele schrieb:
Dein gezeigter Code
Code: 
metadata.xml_string.value('(metadata/fields/cusInt04)', 'varchar(50)')
würde glaube ich in MSSQL so gehen, du scheinst auf einem guten Weg zu sein...
Zum Vergrößern anklicken....

Hallo ukulele,
danke sehr für deine Antwort.

Es ist für mich ermutigend zu höhren, dass der Weg nicht komplett in die falsche Richtung geht.
Dann muss ich kurzfristig erstmal ein Workaround machen und langfristig bleibe ich dran und versuche eine Lösung zu finden.

Sollte ich die Lösung gefunden haben, werde ich sie hier auf jeden Fall posten, damit jemand vielleicht davon auch profitieren kann.

Viele Grüße
Dimitrij
 
Hallo,
ich bin der Lösung sehr nahe. Mit Hilfe von unseren DB Experten vor Ort und viel Googeln. Ich versuche es mal zu erklären, hoffe es gelingt mir halbwegs.

Ich möchte ja den Inhalt aus der XML Deklaration abrufen. Am obigen Beispiel der XML-Deklaration (Bild im obigen Post) und der gewünschten Abfrage erhalte ich folgenden Query:
  1. SELECT
  2. items.cus_int_04, t.xmlc.value('(metadata/fields/field[@name="cusInt04"]/option[@value="1"])[1]', 'varchar(20)')
  3. FROM
  4. 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
In Zeile 2 benutze ich die value methode und gebe den Knotenpunkt an, von wo ich den Wert zurück gegeben haben möchte.
In Zeile 4 mache ich drei Schritte:
a) XML-SQL-Funktionen arbeiten nur mit XML-Datenspalten, aber die Inhalte in den Zellen der SQL-Spalte "XML_STRING" sind Texte, also muss ich xml konvertieren -> CAST (<column> AS xml) AS Name
b) XML mag keine Umlaute deshalb werden sie mit den "normalen" Zeichen ersetzt. Ich habe erstmal die üblichen Deutschen Sonder-Buchstaben angegeben
c) "xml_string"ist eine Text-Spalte aber die "replace" Funktion arbeitet nicht mit Text, sodass es in varchar konvertiert werden muss, mit max. Länge.

Leider bekome ich dabei ein starres Ergebniss. Der zurück geliefert Wert aus XML ist NUR der Wert unter der "option=1", welcher ist im obigen Beispiel "AAA". Danach frage ich ja.
Das bedeutet, dass ich die "1" durch eine Variable ersetzen muss. Das gelingt mir, indem ich erstmal eine Variable definiere und diese dann mit der funktion sql:variable() in die Abfrage einbringe:


  1. declare @var1 varchar(10)
  2. set @var1 = '1'
  3. SELECT
  4. items.cus_int_04, t.xmlc.value('(metadata/fields/field[@name="cusInt04"]/option[@value=sql:variable("@var1")])[1]', 'varchar(20)')
  5. FROM
  6. 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
In Zeile 1 deklariere ich die Variabla "var1" als varchar.
In Zeile 2 setze ich den Wert dieser Variable = 1
In Zeile 4 den Wert der option nicht als starre "1" sondern stattdessen die Variable.

Das Ergebniss ist bis jetzt der gleiche, denn die Variable hat ja den Wert = 1.


JETZT fehlt der letzte Schritt, den ich nicht hinbekomme.
Ich muss statt der var1 den Wert "1" vorzugeben, sagen, dass die Werte aus der Spalte cus_int_04 kommen, irgend wie so var1 = items.cus_int_04.

Ich hoffe, dass ich meine Erklärung jemanden mit ähnlichen Problemen weiter helfen kann und hoffe gleichzeitig, dass hier jemand diesen letzten Schritt kennt, der mir fehlt.

Danke und viele Grüße
Dimitrij
 
Werbung:
Und just in dem Moment, wo ich den vorherigen Post abgeschickt habe, kamm bei mir eine Idee.
Ich suchte nach SQL Funktionen und habe das Richtge gefunden, so dass es jetzt funktioniert.

Anstatt die Funktion sql:variable() verwende ich nun Funktion sql:column(). Dabei nimmt er an dieser Stelle die Werte aus der Spalte, die ich im vorgebe. So sieht der fertige Code aus:

SELECT
items.cus_int_04, t.xmlc.value('(metadata/fields/field[@name="cusInt04"]/option[@value=sql:column("items.cus_int_04")])[1]', 'varchar(20)')
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

Vielen Dank nochmal für eure Unterstützung und ich hoffe, dass diese Lösung eines Tages auch jemandem helfen kann.
Viele Grüße
Dimitrij
 
Zurück
Oben