enrique_aeo
asked on
xml: number of rows in openxml
I have this XML
declare @hind int
declare @xmldoc nvarchar(1000)
set @xmldoc = N' <Inventory>
<Item ProductID = "10" ProductDesc = "Aluminum Beam, Angle">
<Trx TrxID = "11" TrxDate = "4/19/2008" />
<Trx TrxID = "18" TrxDate = "4/27/2008" />
</Item>
<Item ProductID = "11" ProductDesc = "Steam Beam, Angle">
<Trx TrxID = "25" TrxDate = "1/1/2008" />
<Trx TrxID = "27" TrxDate = "2/13/2008" />
</Item>
</Inventory>'
query 1
exec sp_xml_preparedocument @hind output, @xmldoc
select *
from openxml (@hind,'Inventory/Item',2)
with
(
ProductID int '@ProductID',
TrxID int './Item/@Trx',
TrxDate varchar(10) './Item/@TrxDate'
)
exec sp_xml_removedocument @hind
only show productid 10 and 11
query 2
exec sp_xml_preparedocument @hind output, @xmldoc
select *
from openxml (@hind,'Inventory/Item',2)
with
(
ProductID int '@ProductID',
TrxID int './Item/@Trx',
TrxDate varchar(10) './Item/@TrxDate'
)
exec sp_xml_removedocument @hind
only show productid 10 and 11 (2 rows)
query 2
exec sp_xml_preparedocument @hind output, @xmldoc
select *
from openxml (@hind,'Inventory/Item/Trx ',1)
with
(
ProductID int '../@ProductID',
TrxID int,
TrxDate varchar(10)
)
exec sp_xml_removedocument @hind
show productid 10 and 11 two times (4 rows)
what is the difference? 2 and 4 rows?
declare @hind int
declare @xmldoc nvarchar(1000)
set @xmldoc = N' <Inventory>
<Item ProductID = "10" ProductDesc = "Aluminum Beam, Angle">
<Trx TrxID = "11" TrxDate = "4/19/2008" />
<Trx TrxID = "18" TrxDate = "4/27/2008" />
</Item>
<Item ProductID = "11" ProductDesc = "Steam Beam, Angle">
<Trx TrxID = "25" TrxDate = "1/1/2008" />
<Trx TrxID = "27" TrxDate = "2/13/2008" />
</Item>
</Inventory>'
query 1
exec sp_xml_preparedocument @hind output, @xmldoc
select *
from openxml (@hind,'Inventory/Item',2)
with
(
ProductID int '@ProductID',
TrxID int './Item/@Trx',
TrxDate varchar(10) './Item/@TrxDate'
)
exec sp_xml_removedocument @hind
only show productid 10 and 11
query 2
exec sp_xml_preparedocument @hind output, @xmldoc
select *
from openxml (@hind,'Inventory/Item',2)
with
(
ProductID int '@ProductID',
TrxID int './Item/@Trx',
TrxDate varchar(10) './Item/@TrxDate'
)
exec sp_xml_removedocument @hind
only show productid 10 and 11 (2 rows)
query 2
exec sp_xml_preparedocument @hind output, @xmldoc
select *
from openxml (@hind,'Inventory/Item/Trx
with
(
ProductID int '../@ProductID',
TrxID int,
TrxDate varchar(10)
)
exec sp_xml_removedocument @hind
show productid 10 and 11 two times (4 rows)
what is the difference? 2 and 4 rows?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.