Refering to a parent element with openxml

I have this XML structure:
<Branch Number = "1">
<Supply Total = "2"/>
<Supply Total = "6"/>
<Supply Total = "2"/>
</Branch>
I can successfully use openxml to select all the supply records and insert
them in to a table using
insert into
Supply
select
Total
from
OPENXML
( @.iDoc, @.XPath, 1 )
with (
Total smallint,
)
But how can I reference the Number attribute in the parent Branch element as
follows:
insert into
Supply
select
BranchNumber
Total
from
...
Thanks!
Here's one way:
declare @.xml varchar(2000)
set @.xml = '<Branch Number = "1">
<Supply Total = "2"/>
<Supply Total = "6"/>
<Supply Total = "2"/>
</Branch>'
declare @.hdoc int
exec sp_xml_preparedocument @.hdoc output, @.xml
select *
from openxml(@.hdoc, 'Branch/Supply', 1)
with (
total smallint '@.Total',
branch smallint '../@.Number'
)
exec sp_xml_removedocument @.hdoc
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Xerox" <anon@.anon.com> wrote in message
news:OwyTBR$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
> I have this XML structure:
> <Branch Number = "1">
> <Supply Total = "2"/>
> <Supply Total = "6"/>
> <Supply Total = "2"/>
> </Branch>
> I can successfully use openxml to select all the supply records and insert
> them in to a table using
> insert into
> Supply
> select
> Total
> from
> OPENXML
> ( @.iDoc, @.XPath, 1 )
> with (
> Total smallint,
> )
> But how can I reference the Number attribute in the parent Branch element
as
> follows:
> insert into
> Supply
> select
> BranchNumber
> Total
> from
> ...
> Thanks!
>
|||Thats great! Thanks Adam
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uT#6je$IFHA.2604@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Here's one way:
>
> declare @.xml varchar(2000)
> set @.xml = '<Branch Number = "1">
> <Supply Total = "2"/>
> <Supply Total = "6"/>
> <Supply Total = "2"/>
> </Branch>'
> declare @.hdoc int
> exec sp_xml_preparedocument @.hdoc output, @.xml
> select *
> from openxml(@.hdoc, 'Branch/Supply', 1)
> with (
> total smallint '@.Total',
> branch smallint '../@.Number'
> )
> exec sp_xml_removedocument @.hdoc
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Xerox" <anon@.anon.com> wrote in message
> news:OwyTBR$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
insert[vbcol=seagreen]
element
> as
>

Refering to a parent element with openxml

I have this XML structure:
<Branch Number = "1">
<Supply Total = "2"/>
<Supply Total = "6"/>
<Supply Total = "2"/>
</Branch>
I can successfully use openxml to select all the supply records and insert
them in to a table using
insert into
Supply
select
Total
from
OPENXML
( @.iDoc, @.XPath, 1 )
with (
Total smallint,
)
But how can I reference the Number attribute in the parent Branch element as
follows:
insert into
Supply
select
BranchNumber
Total
from
...
Thanks!Here's one way:
declare @.xml varchar(2000)
set @.xml = '<Branch Number = "1">
<Supply Total = "2"/>
<Supply Total = "6"/>
<Supply Total = "2"/>
</Branch>'
declare @.hdoc int
exec sp_xml_preparedocument @.hdoc output, @.xml
select *
from openxml(@.hdoc, 'Branch/Supply', 1)
with (
total smallint '@.Total',
branch smallint '../@.Number'
)
exec sp_xml_removedocument @.hdoc
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Xerox" <anon@.anon.com> wrote in message
news:OwyTBR$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
> I have this XML structure:
> <Branch Number = "1">
> <Supply Total = "2"/>
> <Supply Total = "6"/>
> <Supply Total = "2"/>
> </Branch>
> I can successfully use openxml to select all the supply records and insert
> them in to a table using
> insert into
> Supply
> select
> Total
> from
> OPENXML
> ( @.iDoc, @.XPath, 1 )
> with (
> Total smallint,
> )
> But how can I reference the Number attribute in the parent Branch element
as
> follows:
> insert into
> Supply
> select
> BranchNumber
> Total
> from
> ...
> Thanks!
>|||Thats great! Thanks Adam
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uT#6je$IFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Here's one way:
>
> declare @.xml varchar(2000)
> set @.xml = '<Branch Number = "1">
> <Supply Total = "2"/>
> <Supply Total = "6"/>
> <Supply Total = "2"/>
> </Branch>'
> declare @.hdoc int
> exec sp_xml_preparedocument @.hdoc output, @.xml
> select *
> from openxml(@.hdoc, 'Branch/Supply', 1)
> with (
> total smallint '@.Total',
> branch smallint '../@.Number'
> )
> exec sp_xml_removedocument @.hdoc
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Xerox" <anon@.anon.com> wrote in message
> news:OwyTBR$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
insert
element
> as
>

referencing value in subreport in calculated field on parent

I wish to reference the value in a subreport in a calculated field
elsewhere...
this subreport has one txtbox, no grid or any other objects.
is this possible?
what is the syntax...'
i.e. =mysubReport.mytxtbox.value...........
thanksOn Sep 28, 2:51 pm, r...@.mgk.com wrote:
> I wish to reference the value in a subreport in a calculated field
> elsewhere...
> this subreport has one txtbox, no grid or any other objects.
> is this possible?
> what is the syntax...'
> i.e. =mysubReport.mytxtbox.value...........
> thanks
You will need to create a dataset in the main report that accesses the
same data (i.e., query/stored procedure) that is used in the subreport
and add the calculation the same way (basically duplicating the
efforts). Then, in the main report, reference this dataset via an
aggregate expression, for example:
=Max(Fields!SomeFieldName.Value, "NewDataSetName")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Sep 29, 10:27 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Sep 28, 2:51 pm, r...@.mgk.com wrote:
> > I wish to reference the value in a subreport in a calculated field
> > elsewhere...
> > this subreport has one txtbox, no grid or any other objects.
> > is this possible?
> > what is the syntax...'
> > i.e. =mysubReport.mytxtbox.value...........
> > thanks
> You will need to create a dataset in the main report that accesses the
> same data (i.e., query/stored procedure) that is used in the subreport
> and add the calculation the same way (basically duplicating the
> efforts). Then, in the main report, reference this dataset via an
> aggregate expression, for example:
> =Max(Fields!SomeFieldName.Value, "NewDataSetName")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
argh! i was afraid of that...sql