XQUERY retur

[Chance1234]

<recurrence><rule>
   <firstDayOfWeek>su</firstDayOfWeek>
   <repeat><monthly monthFrequency='2'
      day='3' /></repeat>
   <repeatInstances>5</repeatInstances>
</rule></recurrence>

<recurrence><rule>
   <firstDayOfWeek>su</firstDayOfWeek>
   <repeat><daily dayFrequency='2' 
      /></repeat>
   <windowEnd>2003-09-
      20T09:00:00Z</windowEnd>
</rule></recurrence>

<recurrence><rule>
   <firstDayOfWeek>su</firstDayOfWeek>
   <repeat><yearly yearFrequency='1' 
      month='9' day='20' /></repeat>
   <windowEnd>2007-09-
      20T07:00:00Z</windowEnd>
</rule></recurrence>

The above are examples of the xml data that im working with,

I am ok with pulling back the values

declare @xml xml
set @xml = '<recurrence><rule><firstDayOfWeek>su</firstDayOfWeek><repeat><daily dayFrequency="1" /></repeat><repeatForever>FALSE</repeatForever></rule></recurrence>'

declare @firstday varchar(10)
select @xml.query('//recurrence/rule/firstDayOfWeek').value('.','varchar(10)')

However, I am struggling to pull out the data between the repeat node.

I ideally would like to bring it back so I know whether its Daily / Weekly / Monthly / Yearly

Then bring back the interval information.

Ive been searchign through Bol for ages and cant see how i can do this , any help appreciated.




Chance,

F, G + 1MSTG

 

[Chance1234]

got a bit further with the follownig

declare @idoc int
declare @doc varchar(1000)

Set @doc = '<recurrence><rule><firstDayOfWeek>su</firstDayOfWeek><repeat><daily dayFrequency="1" /></repeat><repeatForever>FALSE</repeatForever></rule></recurrence>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc 

SELECT localname,text
FROM OPENXML(@idoc, '//recurrence/rule/repeat',2)

Chance,

F, G + 1MSTG

 

[tsuji]

>I ideally would like to bring it back so I know whether its Daily / Weekly / Monthly / Yearly

[1] In xquery it is something like this in parallel with the first approach.
[tt]
let $xml := document {
<recurrence>
<rule>
<firstDayOfWeek>su</firstDayOfWeek>
<repeat><daily dayFrequency="1" /></repeat>
<repeatForever>FALSE</repeatForever>
</rule>
</recurrence>
}
let $nodes := $xml/recurrence/rule/repeat/*
return $nodes[1]/name()
[/tt]
[2] In sql/xml I would suggest it be something like this - no guarantee.
[tt]
declare @xml xml
set @xml = '<recurrence><rule><firstDayOfWeek>su</firstDayOfWeek><repeat><daily dayFrequency="1" /></repeat><repeatForever>FALSE</repeatForever></rule></recurrence>'

declare @nodename varchar(max)
select @xml.query('/recurrence/rule/repeat/*[1]').value('name(.)','varchar(max)')
[/tt]
It should be close, maybe look into it along this line?