Extract a complete XML section and sub sections

Ive been using select statments to get information from each 'section'.
example below shows only for the Header section.
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
the following section of the file has sub sections contained within the
Header section, Is there a simple way to return all the data values in one
SQL ?
- <Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>
You can specify relative XPaths for the columns in the subelements, as shown
below. Is that what you mean?
DECLARE @.TESTXML nvarchar(2000)
DECLARE @.hDoc integer
SET @.TESTXML =
'<Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>'
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header', 1)
with
(reportType varchar(10),
reportNumber VarChar(6),
batchNumber varchar(6),
reportSequenceNumber varchar(6),
userNumber varchar(6),
ProducedOnTime nvarchar(10) 'ProducedOn/@.time',
ProducedOnDate nvarchar(20) 'ProducedOn/@.date',
ProcessingDate nvarchar(20) 'ProcessingDate/@.date' )
EXEC sp_xml_removedocument @.hDoc
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:4CD137BC-09E8-42A8-BC86-6C6991EA979C@.microsoft.com...
Ive been using select statments to get information from each 'section'.
example below shows only for the Header section.
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber
varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
the following section of the file has sub sections contained within the
Header section, Is there a simple way to return all the data values in one
SQL ?
- <Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>
sql

expression problem

Hi There
I am trying to sum the amount value in the budgets dataset based on a sub
catagory within the budgets table called personal loans.
my expression is
=Sum iif(Fields!Sub_Catagory.Value, "budgets" = "Personal Loans",
Fields!Amount.Value, "budgets", Nothing))
this sits within an actuals table that uses a different dataset
could i please have some assistance as to where i am going wrong. error
message is The value expression for the textbox â'total_budgetâ' refers to the
field â'Sub_Catagoryâ'. Report item expressions can only refer to fields
within the current data set scope or, if inside an aggregate, the specified
data set scope.
ThankyouIf I understood you right: You want to sum the Amount (within one dataset)
if the sub_category is "Personal Loans" (within another dataset). try this:
= Sum(iif(Fields!Sub_Category.Value = "Personal Loans", Fields!Amount.Value,
0))
syntax:
iif( expression, what to do if the expression is true, what to do if the
expression is false)
I wasn't able to test the expression, because i have no report server
running at the moment.
"Tango" <Tango@.discussions.microsoft.com> schrieb im Newsbeitrag
news:EE09B408-6D43-4774-BC61-8D5A999EDF4B@.microsoft.com...
> Hi There
> I am trying to sum the amount value in the budgets dataset based on a sub
> catagory within the budgets table called personal loans.
> my expression is
> =Sum iif(Fields!Sub_Catagory.Value, "budgets" = "Personal Loans",
> Fields!Amount.Value, "budgets", Nothing))
> this sits within an actuals table that uses a different dataset
> could i please have some assistance as to where i am going wrong. error
> message is The value expression for the textbox 'total_budget' refers to
> the
> field 'Sub_Catagory'. Report item expressions can only refer to fields
> within the current data set scope or, if inside an aggregate, the
> specified
> data set scope.
> Thankyou
>
>|||The following link discusses conditional expressions:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3983.asp
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jens Konerow" <keineangabe@.web.de> wrote in message
news:%23RfrfWqwFHA.3556@.TK2MSFTNGP15.phx.gbl...
> If I understood you right: You want to sum the Amount (within one dataset)
> if the sub_category is "Personal Loans" (within another dataset). try
> this:
> = Sum(iif(Fields!Sub_Category.Value = "Personal Loans",
> Fields!Amount.Value, 0))
> syntax:
> iif( expression, what to do if the expression is true, what to do if the
> expression is false)
> I wasn't able to test the expression, because i have no report server
> running at the moment.
> "Tango" <Tango@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:EE09B408-6D43-4774-BC61-8D5A999EDF4B@.microsoft.com...
>> Hi There
>> I am trying to sum the amount value in the budgets dataset based on a sub
>> catagory within the budgets table called personal loans.
>> my expression is
>> =Sum iif(Fields!Sub_Catagory.Value, "budgets" = "Personal Loans",
>> Fields!Amount.Value, "budgets", Nothing))
>> this sits within an actuals table that uses a different dataset
>> could i please have some assistance as to where i am going wrong. error
>> message is The value expression for the textbox 'total_budget' refers to
>> the
>> field 'Sub_Catagory'. Report item expressions can only refer to fields
>> within the current data set scope or, if inside an aggregate, the
>> specified
>> data set scope.
>> Thankyou
>>
>