February 5, 2017 at 9:36 am
I have xml like this :
DECLARE @x XML = '<revieves>
<recievemessages>
<message>test</message>
<to>111111</to>
<froms>
<from>123</from>
<from>546</from>
</froms>
</recievemessages>
<recievemessages>
<message>test2</message>
<to>22222</to>
<froms>
<from>678</from>
<from>890</from>
</froms>
</recievemessages>
</revieves>'
I want to have result like this :
message to from
test 111111 123
test 111111 546
test2 22222 678
test2 22222 890
this query doesnt work
SELECT t.c.value('(message/text())[1]', 'nvarchar(100)') AS [message] ,
t.c.value('(to/text())[1]', 'nvarchar(100)') AS [to],
t.c.value('(froms/from/node())[1]', 'nvarchar(100)') AS [from]
FROM @x.nodes('/revieves/recievemessages') AS t ( c )
February 5, 2017 at 1:50 pm
Try this:SELECT t.c.value('(message/text())[1]', 'nvarchar(100)') AS [message] ,
t.c.value('(to/text())[1]', 'nvarchar(100)') AS [to],
b.d.value('(text())[1]', 'nvarchar(100)') AS [from]
FROM @x.nodes('/revieves/recievemessages') AS t(c)
CROSS APPLY t.c.nodes('froms/from') AS b(d);
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 6, 2017 at 12:45 am
Out of curiosity, why duplicate the results?
😎
You could do something like thisDECLARE @x XML = '<revieves>
<recievemessages>
<message>test</message>
<to>111111</to>
<froms>
<from>123</from>
<from>546</from>
</froms>
</recievemessages>
<recievemessages>
<message>test2</message>
<to>22222</to>
<froms>
<from>678</from>
<from>890</from>
</froms>
</recievemessages>
</revieves>';
SELECT
RMSG.DATA.value('(message/text())[1]', 'nvarchar(100)') AS [message]
,RMSG.DATA.value('(to/text())[1]', 'nvarchar(100)') AS [to]
,RMSG.DATA.value('(froms/from/text())[1]', 'nvarchar(100)') AS [from1]
,RMSG.DATA.value('(froms/from/text())[2]', 'nvarchar(100)') AS [from2]
FROM @x.nodes('/revieves/recievemessages') AS RMSG(DATA);
Which brings back all the data from the XML
message to from1 from2
--------- -------- ------- ------
test 111111 123 546
test2 22222 678 890
February 6, 2017 at 2:02 am
Eirikur Eiriksson - Monday, February 6, 2017 12:45 AMOut of curiosity, why duplicate the results?
😎
That data set you have doesn't really give what the OP asked for though, as the second result is in a column rather than row. Mine also allows for only 1 from, or more than 2 (and we all know that an OPs "sample" data can often only display 1 of several scenarios 🙂 ).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 6, 2017 at 2:12 am
Thom A - Monday, February 6, 2017 2:02 AMEirikur Eiriksson - Monday, February 6, 2017 12:45 AMOut of curiosity, why duplicate the results?
😎That data set you have doesn't really give what the OP asked for though, as the second result is in a column rather than row. Mine also allows for only 1 from, or more than 2 (and we all know that an OPs "sample" data can often only display 1 of several scenarios 🙂 ).
I know that your query is what the OP asked for, I'm just being curious why duplicating the three columns when all the same data can be brought back without the duplication.
😎
February 6, 2017 at 9:48 am
Eirikur Eiriksson - Monday, February 6, 2017 2:12 AMThom A - Monday, February 6, 2017 2:02 AMEirikur Eiriksson - Monday, February 6, 2017 12:45 AMOut of curiosity, why duplicate the results?
😎That data set you have doesn't really give what the OP asked for though, as the second result is in a column rather than row. Mine also allows for only 1 from, or more than 2 (and we all know that an OPs "sample" data can often only display 1 of several scenarios 🙂 ).
I know that your query is what the OP asked for, I'm just being curious why duplicating the three columns when all the same data can be brought back without the duplication.
😎
Because the first one is normalized and the second one is not.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply