Link to home
Start Free TrialLog in
Avatar of jflanner
jflanner

asked on

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

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:  https://www.experts-exchange.com/questions/20177696/strange-result.html?query=xml+result+set&topics=42
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial