-2

Given the below XML, what would be the proper SQL XQuery to retrieve the SubscriberStatus where the SubscriberID is empty? Given the XML is stored in a column with the XML datatype.

<ObjectEntry> <Key>Key1</Key> <DicValue> <ObjectEntry> <Key>SubscriberStatus</Key> <Value xsi:type="xsd:string">Active</Value> <DicValue /> </ObjectEntry> <ObjectEntry> <Key>SubscriberID</Key> <Value xsi:type="xsd:string" /> <DicValue /> </ObjectEntry> </DicValue> </ObjectEntry>

Steve Wall
  • 1,842
  • 5
  • 21
  • 50
  • There is no such thing like SQL XQuery. There simply are relational databases which also support XQuery. In this context, there is no difference whether you execute the XQuery in a relational database or somewhere else. – dirkk Jun 13 '14 at 10:18
  • -1 for not showing an attempt to solve your problem. – DBedrenko Jun 13 '14 at 10:48

3 Answers3

1

Try this:

If $node holds your xml fragment then

$node//ObjectEntry[DicValue/ObjectEntry[Key eq "SubscriberStatus"] and DicValue/ObjectEntry[Key eq "SubscriberID"][Value ne ""]]

will give you back the ObjectEntry parent for the non empty SubscriberIDs

prker
  • 494
  • 2
  • 3
1

This is a simply XPath expression, there is no need for true XQuery. XPath is a subset of XQuery. Given that you want the <Value/> element of the SubscriberStatus you can get it like the following:

//ObjectEntry/DicValue[ObjectEntry[Key = "SubscriberID"]/Value = ""]/ObjectEntry[Key = "SubscriberStatus"]/Value

This fetches all ObjectEntries which do have an empty SubscriberID and then navigates to the SubscriberStatus. If you just want the actual string, you cann append /string()

dirkk
  • 6,160
  • 5
  • 33
  • 51
0

Thanks for the suggestions! Unfortunately they didn't do what I was asking, but did help me get the right syntax. Here's a solution that works.

select Request.query('//ObjectEntry/DicValue/ObjectEntry[Key = "SubscriberStatus"]/Value') as SubscriberStatus from RequestLog where Request.exist('//ObjectEntry/DicValue[ObjectEntry[Key = "SubscriberID" and Value = ""]]') = 1

Steve Wall
  • 1,842
  • 5
  • 21
  • 50