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</Co st></LineI tem>
<LineItem><Item>Wicker Basket</Item><Cost>1.65</C ost></Line Item>
</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/LineI tem/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</Empl oyeeID><Li neItems><L ineItem><I tem>Boat</ Item><Cost >4268.65</ Cost></Lin eItem><Lin eItem><Ite m>Space Shuttle</Item><Cost>452374 5341.65</C ost></Line Item></Lin eItems></S ale>')
insert into Sale (EmployeeID, StoreID, Contract)
values (1, 2, '<Sale><EmployeeID>1</Empl oyeeID><Li neItems><L ineItem><I tem>Plain< /Item><Cos t>44268.65 </Cost></L ineItem><L ineItem><I tem>Train< /Item><Cos t>745341.6 5</Cost></ LineItem>< /LineItems ></Sale>')
insert into Sale (EmployeeID, StoreID, Contract)
values (2, 2, '<Sale><EmployeeID>2</Empl oyeeID><Li neItems><L ineItem><I tem>Tricyc le</Item>< Cost>28.65 </Cost></L ineItem><L ineItem><I tem>Wicker Basket</Item><Cost>1.65</C ost></Line Item></Lin eItems></S ale>')
select contract.query('for $var in data(/Sale/LineItems/LineI tem/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
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</
<LineItem><Item>Wicker Basket</Item><Cost>1.65</C
</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/LineI
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</Empl
insert into Sale (EmployeeID, StoreID, Contract)
values (1, 2, '<Sale><EmployeeID>1</Empl
insert into Sale (EmployeeID, StoreID, Contract)
values (2, 2, '<Sale><EmployeeID>2</Empl
select contract.query('for $var in data(/Sale/LineItems/LineI
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.