?
Solved

xml: number of rows in openxml

Posted on 2011-03-09
1
Medium Priority
?
1,155 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
1 Comment
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 1000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 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