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 a total for a group inside that group?

Is there any way to do this' I am trying to display percentages of the total
along with the number of items. What I want to do would look something like
this.
Group header:
Detail X X/Sum of all X's
Group footer Sum of all X's
I can't seem to find a way to refer to the item in the footer inside the
group. Can this be done? If so how?
Thanks in advance.I found it!
What I had to do was:
Group header:
Detail X X/Sum(X,"Groupname")
Group footer Sum of all X's
In case anyone has the same problem.
"Doug" wrote:
> Is there any way to do this' I am trying to display percentages of the total
> along with the number of items. What I want to do would look something like
> this.
> Group header:
> Detail X X/Sum of all X's
> Group footer Sum of all X's
> I can't seem to find a way to refer to the item in the footer inside the
> group. Can this be done? If so how?
> Thanks in advance.
>