?
Solved

XQuery returns one row where XML item has many occurrences.  (SQL Server 2005)

Posted on 2006-03-25
1
Medium Priority
?
241 Views
Last Modified: 2008-03-10
folks:

I'm trying to learn SQL server 2005 XML support.  As an academic exercise, I put together the below script.  Everything is working fine except when i do my xquery.  What I'm getting back is one row containing a concatination of the strings I'm trying to query out.  Specifically, I get:
Item
-----------------------------
Tricycle Wicker Basket

(1 row(s) affected)

where I want
-----------------------------
Tricycle
Wicker Basket

(2 row(s) affected)

The XML I'm querying against is:
<Sale>
<EmployeeID>2</EmployeeID>
<LineItems>
<LineItem><Item>Tricycle</Item><Cost>28.65</Cost></LineItem>
<LineItem><Item>Wicker Basket</Item><Cost>1.65</Cost></LineItem>
</LineItems>
</Sale>
This is stored in an XML datatype column "Contract" in the "Sale" table.

The xquery statement is:
select contract.query('for $var in data(/Sale/LineItems/LineItem/Item) return string($var)') as Item
    from Sale
    where EmployeeID = 2

I appreciate any advice you can give me.




If it helps - the entire script is:
CREATE TABLE Employee
      (
           EmployeeID int identity(1,1) NOT NULL primary key,
           EmployeeName varchar(50) NOT NULL
      )
GO

CREATE TABLE Sale
    (
         SaleID int identity(1,1) not null primary key,
         EmployeeID int not null,
         StoreId int not null,
         Contract xml not null
     )
go
ALTER TABLE Sale ADD CONSTRAINT
      FK_Sale_Employee FOREIGN KEY
      (
          EmployeeID
      )
    REFERENCES Employee
      (
          EmployeeID
      )
GO


insert into Employee (EmployeeName)
   values('John')
go
insert into Employee (EmployeeName)
   values('Rich')
go

insert into Sale (EmployeeID, StoreID, Contract)
   values (1, 2, '<Sale><EmployeeID>1</EmployeeID><LineItems><LineItem><Item>Boat</Item><Cost>4268.65</Cost></LineItem><LineItem><Item>Space Shuttle</Item><Cost>4523745341.65</Cost></LineItem></LineItems></Sale>')
insert into Sale (EmployeeID, StoreID, Contract)
   values (1, 2, '<Sale><EmployeeID>1</EmployeeID><LineItems><LineItem><Item>Plain</Item><Cost>44268.65</Cost></LineItem><LineItem><Item>Train</Item><Cost>745341.65</Cost></LineItem></LineItems></Sale>')
insert into Sale (EmployeeID, StoreID, Contract)
   values (2, 2, '<Sale><EmployeeID>2</EmployeeID><LineItems><LineItem><Item>Tricycle</Item><Cost>28.65</Cost></LineItem><LineItem><Item>Wicker Basket</Item><Cost>1.65</Cost></LineItem></LineItems></Sale>')

select contract.query('for $var in data(/Sale/LineItems/LineItem/Item) return string($var)') as Item
    from Sale
    where EmployeeID = 2


drop table Sale
go
drop table Employee
go



SQl Server 2000 solution:  http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20177696.html?query=xml+result+set&topics=42
0
Comment
Question by:jflanner
1 Comment
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 16289433
If the query method returns an untyped XML and you want a resultset, should you not be using OPENXML?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 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