August 13, 2014 at 8:51 am
I have the below xml column in database :
<row id="10000000" xml:space="preserve">
<c1>Name 1</c1>
<c2>Name 1</c2>
<c10>40</c10>
<c10 m="2">40</c10>
<c10 m="3">40</c10>
<c10 m="4">40</c10>
<c10 m="5">42</c10>
<c10 m="6">20</c10>
<c10 m="7">20</c10>
<c10 m="8">19</c10>
<c10 m="9">21</c10>
<c10 m="10">18</c10>
<c11>10000025</c11>
<c11 m="2">10000001</c11>
<c11 m="3">10000002</c11>
<c11 m="4">10000044</c11>
<c11 m="5">10000000</c11>
<c11 m="6">10000026</c11>
<c11 m="7">10000042</c11>
<c11 m="8">10000067</c11>
<c11 m="9">10000066</c11>
<c11 m="10">1000012</c11>
</row>
My problem is :
I want to search the above xml to find 19 inside the c10 element and return the corresponding in c11.
The result that i want to be is :
19 | 10000067
Can anyone help?
September 4, 2014 at 3:06 am
Hi,
This code will get the c11 that have the same "m"-attribute as the c10 with the value 19:
-- set up some testdata
declare @xml xml
set @xml = '<row id="10000000" xml:space="preserve">
<c1>Name 1</c1>
<c2>Name 1</c2>
<c10>40</c10>
<c10 m="2">40</c10>
<c10 m="3">40</c10>
<c10 m="4">40</c10>
<c10 m="5">42</c10>
<c10 m="6">20</c10>
<c10 m="7">20</c10>
<c10 m="8">19</c10>
<c10 m="9">21</c10>
<c10 m="10">18</c10>
<c11>10000025</c11>
<c11 m="2">10000001</c11>
<c11 m="3">10000002</c11>
<c11 m="4">10000044</c11>
<c11 m="5">10000000</c11>
<c11 m="6">10000026</c11>
<c11 m="7">10000042</c11>
<c11 m="8">10000067</c11>
<c11 m="9">10000066</c11>
<c11 m="10">1000012</c11>
</row>'
-- Find the c11 corresponding to the c10 vith value 19
select @xml.query('/row/c11[@m=(/row/c10[text()=19]/@m)]')
/M
September 13, 2014 at 1:29 pm
Here is another way of doing this, slightly more flexible
😎
USE TESTDB;
GO
SET NOCOUNT ON;
DECLARE @SEARCH_VALUE INT = 19;
DECLARE @TXML XML
set @TXML = '<row id="10000000" xml:space="preserve">
<c1>Name 1</c1>
<c2>Name 2</c2>
<c10>40</c10>
<c10 m="2">40</c10>
<c10 m="3">40</c10>
<c10 m="4">40</c10>
<c10 m="5">42</c10>
<c10 m="6">20</c10>
<c10 m="7">20</c10>
<c10 m="8">19</c10>
<c10 m="9">21</c10>
<c10 m="10">18</c10>
<c11>10000025</c11>
<c11 m="2">10000001</c11>
<c11 m="3">10000002</c11>
<c11 m="4">10000044</c11>
<c11 m="5">10000000</c11>
<c11 m="6">10000026</c11>
<c11 m="7">10000042</c11>
<c11 m="8">10000067</c11>
<c11 m="9">10000066</c11>
<c11 m="10">1000012</c11>
</row>'
SELECT
XROW.DATA.value('@id','VARCHAR(12)') AS R_ID
,C1.DATA.value('.[1]','VARCHAR(25)') AS C1_VALUE
,C2.DATA.value('.[1]','VARCHAR(25)') AS C2_VALUE
,C10.DATA.value('.[1]','VARCHAR(25)') AS C10_VALUE
,C11.DATA.value('.[1]','VARCHAR(25)') AS C11_VALUE
FROM @TXML.nodes('row') AS XROW(DATA)
OUTER APPLY XROW.DATA.nodes('c1') AS C1(DATA)
OUTER APPLY XROW.DATA.nodes('c2') AS C2(DATA)
OUTER APPLY XROW.DATA.nodes('c10[text()=sql:variable("@SEARCH_VALUE")]') AS C10(DATA)
OUTER APPLY XROW.DATA.nodes('c11[@m=(/row/c10[text()=sql:variable("@SEARCH_VALUE")]/@m)]') AS C11(DATA)
Results
R_ID C1_VALUE C2_VALUE C10_VALUE C11_VALUE
--------- --------- --------- ---------- ----------
10000000 Name 1 Name 2 19 10000067
September 23, 2014 at 1:54 am
[SSCommitted] In your solution, I have to assign both m values. That wasn't the request. Thanks anyway....
September 23, 2014 at 9:17 am
pitioageneral (9/23/2014)
[SSCommitted] In your solution, I have to assign both m values. That wasn't the request. Thanks anyway....
Sorry, made the assumption that you could comment out the unwanted output.
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply