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
>