Solved

xml: number of rows in openxml

Posted on 2011-03-09
1
1,122 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 250 total points
ID: 35096244
Because you are selecting different sets of nodes with the different XPaths (in the OPENXML lines).

In the top two queries, you select nodes using the path "Inventory/Item" and there are two Item nodes; this results in two "rows" being created.

In the bottom query, you select nodes using the path "Inventory/Item/Trx". There are four Trx nodes in the example you provided, so four "rows" are being created.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question