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

Node1 Node2

100 10

200 1

200 2

200 3

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

Jared

SSC Guru

Points: 61793

September 12, 2011 at 1:16 pm

#1380281

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

Node1 Node2

100 10

200 123

and I am expecting O/p as

Node1 Node2

100 10

200 1

200 2

200 3

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

jagat.patel

SSC Eights!

Points: 908

September 12, 2011 at 1:41 pm

#1380297

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

jagat.patel

SSC Eights!

Points: 908

September 13, 2011 at 7:33 am

#1380614

Yes id did help resolve my issue

Once again thanks for your help.