XQuery in Sql Server 2008

Question

Post reply

XQuery in Sql Server 2008

jagat.patel

SSC Eights!

Points: 908
More actions
September 12, 2011 at 12:59 pm

#390388

I have following XML
'<Filter>
<List>
<Node1>100</Node1>
<Node2>10</Node2>
</List>
<List>
<Node1>200</Node1>
<Node2>
<Val>1</Val>
<Val>2</Val>
<Val>3</Val>
</Node2>
</List>
</Filter>'
I am expecting O\P in following format
Node1Node2
10010
2001
2002
2003
using X Query
Thnaks for your Help

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

Can you please be more specific with a question?

Thanks,

Jared

Jared
CE - Microsoft

jagat.patel SSC Eights! Points: 908 More actions · Answer 2

I am running this query

declare @Filt XML='<Filter>

<List>

</List>

<List>

<Node2>

</Node2>

</List>

</Filter>'

SELECT

x.i.value('Node1[1]', 'varchar(1000)') AS Node1,

x.i.value('Node2[1]', 'varchar(1000)') AS Node2

FROM @Filt.nodes('Filter/*') x(i)

Whish gives me o/p as

Node1Node2

10010

200123

and I am expecting O/p as

Node1Node2

10010

2001

2002

2003

Thanks

Jared SSC Guru Points: 61793 More actions · Answer 3

I am not very familiar with xml, but have you tried this?

declare @Filt XML='<Filter>

<List>

</List>

<List>

</List>

<List>

</List>

<List>

</List>

</Filter>'

Thanks,

Jared

Jared
CE - Microsoft

GSquared SSC Guru Points: 260824 More actions · Answer 4

You've got a bit of a problem in that Node2 should either have a direct value or sub-values, but it can be compensated for with an IsNull/Coalesce function call.

The query I came up with looks like this:

DECLARE @XML XML = '<Filter>

<List>

<Node1>100</Node1>

<Node2>10</Node2>

</List>

<List>

<Node1>200</Node1>

<Node2>

<Val>1</Val>

<Val>2</Val>

<Val>3</Val>

</Node2>

</List>

</Filter>' ;

;

WITH ListNodes

AS (SELECT X.N.query('.') AS List

FROM @XML.nodes('/Filter/List') AS X (N))

SELECT List,

List.value('(/List/Node1/text())[1]', 'int') AS Node1,

ISNULL(L2.N.query('.').value('(/Val/text())[1]', 'int'),

List.value('(/List/Node2/text())[1]', 'int')) AS Node2

FROM ListNodes

OUTER APPLY List.nodes('List/Node2/Val') AS L2 (N) ;

It's not beautiful, but it gets the job done. It can probably be improved with an axe by someone who knows XQuery a bit better than me. I fumble around with it and get the job done, but there are others who can make it more efficient.

By the way, this question looks like homework, and I usually don't reply to those, but this one intrigued me enough to make me break the rule. Is it homework? If so, how will you explain to the teacher/prof what you did when he asks about it?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jagat.patel SSC Eights! Points: 908 More actions · Answer 5

Thanks for your Help

This is not Homwork.

I I have similar Business rqeuirement . and to get answer from foram readers I just simplify my xml.

My original XML is really complex one.

Onceagain thanks for your reply.

GSquared SSC Guru Points: 260824 More actions · Answer 6

jagat.patel (9/12/2011)
Thanks for your Help
This is not Homwork.
I I have similar Business rqeuirement . and to get answer from foram readers I just simplify my xml.
My original XML is really complex one.
Onceagain thanks for your reply.

Cool biz. I definitely understand posting simplified/annonymized sample data.

Did my query get you what you need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jagat.patel SSC Eights! Points: 908 More actions · Answer 7

Yes id did help resolve my issue

Once again thanks for your help.