Solved

xml: number of rows in openxml

Posted on 2011-03-09
1
1,098 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 74

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now