-2

I need help with xQuery syntax.

I have a table with a column declarde with the SQLType xml.

My table I would like to create a sql query, using xQuery, to retrieve table records where the xml contains any element, in the xPath, where it's attribute parameterCode = "a specific string"

The xPath is: LoggedProductInfo/LoggedParameterInfo/LoggedDiagnosticObject/DiagnosticObject/TEA2Plus/@parameterCode

The xml is complex, contains sequence, choice etc. My schema

I'm pretty good at SQL, but when it comes to xQuery I'm lost, so I would appreciate any help I could get.

From comments

if I use 

 select a.ReadingId ,
        a.message_xml.exist('LoggedProductInfo/LoggedParameterInfo/L‌​oggedDiagnosticObjec‌​t/DiagnosticObject/T‌​EA2Plus[@parameterCo‌​de = "P1ASV"]') 
     AS parameterCodeExists
 from MyTable a

The value returned for column parameterCodeExists is 0, even if I know there is such a parameterCode.

flag

If I copy the xPath from the xml I'll have the following: 

/lpi:LoggedProductInfo/lpi:LoggedParameterInfo/lpi:LoggedDia‌​gnosticObject[1]/lpi‌​:DiagnosticObject/lp‌​i:TEA2Plus/@paramete‌​rCode

Can it be something special one would need to do due to that lpi:LoggedDiagnosticObject1 is a list?

Hogan
  • 69,564
  • 10
  • 76
  • 117
Daniel Klerfors
  • 1
  • https://stackoverflow.com/help/mcve – Hogan Oct 16 '17 at 15:30
  • You won't get help without showing us the actual (reduced!) XML. It might be enough to state `WITH XMLNAMESPACES('DEFAULT 'lpi'')` right before your query. – Shnugo Oct 19 '17 at 09:15

1 Answers1

0

Update based on comments.

XML is very forgiving and at the same time specific. If you have a typo (miss-spelling or upper lower case miss match) it will not match but not give an error. ANYTHING is legal XML it just might not be what is in your file. This is why schema and validation is so important when working with XML.

I believe the following will do what you want, BUT since you DON'T show us your actual XML file it is kind of hard for me to test.

select a.ReadingId
WHERE   a.message_xml.exist('/lpi:LoggedProductInfo/lpi:LoggedParameterInfo/lpi:LoggedDia‌​gnosticObject[1]/lpi‌​:DiagnosticObject/lp‌​i:TEA2Plus[@parameterCo‌​de = "P1ASV"]')
from MyTable a

--

original answer.

Hint: A condition is checked at the level above in xquery. For example,

LoggedProductInfo/LoggedParameterInfo/LoggedDiagnosticObject/DiagnosticObject/TEA2Plus[@parameterCode = "a specific string"]

should evaluate true or false

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • Thanks, but if I use it as: select a.ReadingId , a.message_xml.exist('LoggedProductInfo/LoggedParameterInfo/LoggedDiagnosticObject/DiagnosticObject/TEA2Plus[@parameterCode = "P1ASV"]') AS parameterCodeExists from MyTable a The value returned for column parameterCodeExists is 0, even if I know there is such a parameterCode. – Daniel Klerfors Oct 17 '17 at 07:42
  • 1
    If I copy the xPath from the xml I'll have the following: /lpi:LoggedProductInfo/lpi:LoggedParameterInfo/lpi:LoggedDiagnosticObject[1]/lpi:DiagnosticObject/lpi:TEA2Plus/@parameterCode Can it be something special one would need to do due to that lpi:LoggedDiagnosticObject[1] is a list? – Daniel Klerfors Oct 17 '17 at 07:42