Ask - xQuery Select Statement

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Ask

Ask questions, discuss or help others by answering


	Ask a question


	Subscribe..

xQuery Select Statement

Aug 8 2011 12:00AM by miteshmakwana

I want to write xQuery to get the ShareClass.Name and NAV.Value. Value of NAV would be based on Highest Date.

It can have

miteshmakwana

818 · 0% · 35

3 Replies

@Mitesh,

I did not understand the question. If you can show a sample XML document and what is the value you are trying to retrieve, I can help you to write a query.

commented on Aug 8 2011 1:08PM

Jacob Sebastian

1 · 100% · 32004

xml

<ShareClasses>
                <ShareClass ID="462" Bloomberg="" LocalCurrency="USD" ISIN="" Name="" PricingFrequency="" Status="true" 
                       Action="U" LaunchDate="" EntryCharge="0" ExitCharge="0" SwitchCharge="0" OngoingCharge="0" PerformanceFee="0" />
                <ShareClass ID="174" Bloomberg="" LocalCurrency="USD" ISIN="LU0290145316" Name="Class I Units" PricingFrequency="" 
                     Status="true" Action="U" LaunchDate="" EntryCharge="0" ExitCharge="0" SwitchCharge="0" OngoingCharge="0" PerformanceFee="0">
                  <NAVs>
                    <NAV Date="2011-05-10" Value="177.7300" Action="U" />
                    <NAV Date="2011-05-17" Value="177.6200" Action="U" />
                  </NAVs>
                </ShareClass>
                <ShareClass ID="175" Bloomberg="" LocalCurrency="EUR" ISIN="LU0290146397" Name="Class I Units" 
                          PricingFrequency="" Status="true" Action="U" LaunchDate="" EntryCharge="0" ExitCharge="0" SwitchCharge="0" 
                          OngoingCharge="0" PerformanceFee="0">
                  <NAVs>
                    <NAV Date="2011-05-10" Value="162.3800" Action="U" />
                    <NAV Date="2011-05-17" Value="162.3100" Action="U" />
                  </NAVs>
                </ShareClass>
              </ShareClasses>

Now I want below table as a result.

ShareClass.Name     Nav.Value     Nav.Action

(Where value of NAV would come from the latest date among all the NAVs under ShareClass)

commented on Aug 9 2011 4:22AM

miteshmakwana

818 · 0% · 35

Is this what you are looking for?

DECLARE @x XML = '
<ShareClasses>
    <ShareClass ID="462" Bloomberg="" LocalCurrency="USD" ISIN="" 
           Name="" PricingFrequency="" Status="true" 
    	   Action="U" LaunchDate="" EntryCharge="0" ExitCharge="0" 
    	   SwitchCharge="0" OngoingCharge="0" PerformanceFee="0" />
    <ShareClass ID="174" Bloomberg="" LocalCurrency="USD" ISIN="LU0290145316" 
         Name="Class I Units" PricingFrequency="" 
    	 Status="true" Action="U" LaunchDate="" EntryCharge="0" ExitCharge="0" 
    	 SwitchCharge="0" OngoingCharge="0" PerformanceFee="0">
      <NAVs>
    	<NAV Date="2011-05-10" Value="177.7300" Action="U" />
    	<NAV Date="2011-05-17" Value="177.6200" Action="U" />
      </NAVs>
    </ShareClass>
    <ShareClass ID="175" Bloomberg="" LocalCurrency="EUR" ISIN="LU0290146397" 
              Name="Class I Units" 
    		  PricingFrequency="" Status="true" Action="U" LaunchDate="" 
    		  EntryCharge="0" ExitCharge="0" SwitchCharge="0" 
    		  OngoingCharge="0" PerformanceFee="0">
      <NAVs>
    	<NAV Date="2011-05-10" Value="162.3800" Action="U" />
    	<NAV Date="2011-05-17" Value="162.3100" Action="U" />
      </NAVs>
    </ShareClass>
</ShareClasses>'

SELECT
    x.value('@ID[1]','INT') AS ID,
    x.value('@Name[1]','VARCHAR(30)') AS Name,
    y.value('@Value[1]', 'MONEY') AS Value,
    y.value('@Action[1]', 'CHAR(1)') AS Action	
FROM @x.nodes('/ShareClasses/ShareClass') a(x)
CROSS APPLY x.nodes('NAVs/NAV') b(y)

/*
ID   Name           Value   Action
---- -------------- ------- ------
174  Class I Units  177.73  U
174  Class I Units  177.62  U
175  Class I Units  162.38  U
175  Class I Units  162.31  U
*/

commented on Aug 10 2011 7:52AM

Jacob Sebastian

1 · 100% · 32004

Your Reply

Sign Up or Login to post a comment.