Solved

xml: number of rows in openxml

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

717 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