sql - XQuery retrieval of a value -
i have following t-sql determines if row exists using 2 criteria:
declare @x xml = ' <row parentid="45" objectid="0" node="root.local.navigation[7]" itemkey="page" itemvalue="confirmation" itemtype="string" /> <row parentid="45" objectid="0" node="root.local.navigation[7]" itemkey="visited" itemvalue="false" itemtype="bool" />' select @x.exist('/row[@node eq "root.local.navigation[7]"] , /row[@itemvalue eq "confirmation"]')
question: given above select, how can select second row's itemvalue? i.e. since there's row node="root.local.navigation[7]" , itemvalue="confirmation", return itemtype value in row node same , itemkey="visited"?
how this:
declare @x xml = ' <row parentid="45" objectid="0" node="root.local.navigation[7]" itemkey="page" itemvalue="confirmation" itemtype="string" /> <row parentid="45" objectid="0" node="root.local.navigation[7]" itemkey="visited" itemvalue="false" itemtype="bool" />' select case when @x.exist('/row[@node eq "root.local.navigation[7]"] , /row[@itemvalue eq "confirmation"]') = 1 @x.value('/row[@node eq "root.local.navigation[7]" , @itemkey eq "visited"][1]/@itemtype', 'varchar(50)') end item_type
Comments
Post a Comment