SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Question about sql xml data type select statement		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fungron
Starting Member

5 Posts
Posted - 01/26/2013 :  03:31:17  Show Profile  Reply with Quote
This is my table structure

USE saleDB2;
GO
CREATE TABLE Product
(
ProductCode nchar(10) not null,
Description nvarchar(500),
STDUnitCost numeric(11,4),
STDUnitCostU nvarchar(5),
STDUnitPrice numeric(11,4),
STDUnitPriceU nvarchar(5),
ValueBalance xml DEFAULT '<value />' NOT NULL,
QtyBalance xml DEFAULT '<qty />' NOT NULL
)

INSERT INTO [Product]
([ProductCode])
VALUES
('AB250')

INSERT INTO [Product]
([ProductCode])
VALUES
('AB100')

UPDATE [Product]
SET ValueBalance.modify('
insert
<Row>
<LocationCode id="TED" />
<Period id="122012" />
<StockValue id="10" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB250'

UPDATE [Product]
SET ValueBalance.modify('
insert
<Row>
<LocationCode id="WHD" />
<Period id="122012" />
<StockValue id="20" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB250'


UPDATE [Product]
SET ValueBalance.modify('
insert
<Row>
<LocationCode id="TED" />
<Period id="122012" />
<StockValue id="10" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB100'

UPDATE [Product]
SET ValueBalance.modify('
insert
<Row>
<LocationCode id="WHD" />
<Period id="122012" />
<StockValue id="20" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB100'


UPDATE Product
SET QtyBalance.modify('
replace value of (/qty/MultiRow[
@color="red"]/@stockQty)[1]
with "20"
')

The below sql statement is used to select the first line of LocationCode in XML

SELECT ValueBalance.value(
'(/value/Row/LocationCode/@id)[1]',
'nvarchar(max)')
AS Location
FROM Product

I want to ask how can i select the all the line of LocationCode??
Thanks for your reply
Edited by - fungron on 01/26/2013 03:32:16

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 01/26/2013 :  04:20:10  Show Profile  Reply with Quote
see illustration below



CREATE TABLE #Product 
( 
ProductCode nchar(10) not null,
Description nvarchar(500),
STDUnitCost numeric(11,4),
STDUnitCostU nvarchar(5),
STDUnitPrice numeric(11,4),
STDUnitPriceU nvarchar(5),
ValueBalance xml DEFAULT '<value />' NOT NULL,
QtyBalance xml DEFAULT '<qty />' NOT NULL
)

INSERT INTO [#Product]
([ProductCode])
VALUES
('AB250')

INSERT INTO [#Product]
([ProductCode])
VALUES
('AB100')

UPDATE [#Product]
SET ValueBalance.modify('
insert 
<Row>
<LocationCode id="TED" />
<Period id="122012" />
<StockValue id="10" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB250'

UPDATE [#Product]
SET ValueBalance.modify('
insert 
<Row>
<LocationCode id="WHD" />
<Period id="122012" />
<StockValue id="20" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB250'


UPDATE [#Product]
SET ValueBalance.modify('
insert 
<Row>
<LocationCode id="TED" />
<Period id="122012" />
<StockValue id="10" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB100'

UPDATE [#Product]
SET ValueBalance.modify('
insert 
<Row>
<LocationCode id="WHD" />
<Period id="122012" />
<StockValue id="20" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB100'


UPDATE #Product
SET QtyBalance.modify('
replace value of (/qty/MultiRow[
@color="red"]/@stockQty)[1]
with "20"
')

SELECT t.u.value('./@id[1]','varchar(10)') 
from #Product p
CROSS APPLY ValueBalance.nodes('/value/Row/LocationCode')t(u)


DROP TABLE #Product

output
--------------------------
TED
WHD
TED
WHD


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

fungron
Starting Member

5 Posts
Posted - 01/26/2013 :  05:51:33  Show Profile  Reply with Quote
Thanks for your help
i don't know CROSS APPLY statement before.
Thanks for your teching
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 01/26/2013 :  05:56:07  Show Profile  Reply with Quote
quote:
Originally posted by fungron

Thanks for your help
i don't know CROSS APPLY statement before.
Thanks for your teching


welcome

see what all you ca do with it

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

fungron
Starting Member

5 Posts
Posted - 01/28/2013 :  04:16:14  Show Profile  Reply with Quote
Sorry, i have question again, also xml select statement

use saleDB3
GO

CREATE TABLE Product
(
ProductCode nchar(10) not null,
Description nvarchar(500),
STDUnitCost numeric(11,4),
STDUnitCostU nvarchar(5),
STDUnitPrice numeric(11,4),
STDUnitPriceU nvarchar(5),
ValueBalance xml DEFAULT '<value />' NOT NULL,
QtyBalance xml DEFAULT '<qty />' NOT NULL
)

INSERT INTO [Product]
([ProductCode])
VALUES
('AB250')

INSERT INTO [Product]
([ProductCode])
VALUES
('AB100')

UPDATE [Product]
SET ValueBalance.modify('
insert
<Row>
<LocationCode id="ddd" />
<Period id="122012" />
<StockValue id="10" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB250'

UPDATE [Product]
SET ValueBalance.modify('
insert
<Row>
<LocationCode id="WHD" />
<Period id="122012" />
<StockValue id="20" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB250'


UPDATE [Product]
SET ValueBalance.modify('
insert
<Row>
<LocationCode id="TED" />
<Period id="122012" />
<StockValue id="10" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB100'

UPDATE [Product]
SET ValueBalance.modify('
insert
<Row>
<LocationCode id="WHD" />
<Period id="122012" />
<StockValue id="20" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB100'


UPDATE [Product]
SET QtyBalance.modify('
insert
<Row>
<LocationCode id="TED" />
<Color id="RED" />
<Period id="122012" />
<StockQty id="10" />
</Row>
as last
into (/qty)[1]
')
WHERE ProductCode='AB100'

UPDATE [Product]
SET QtyBalance.modify('
insert
<Row>
<LocationCode id="WHD" />
<Color id="RED" />
<Period id="122012" />
<StockQty id="5" />
</Row>
as last
into (/qty)[1]
')
WHERE ProductCode='AB100'

UPDATE [Product]
SET QtyBalance.modify('
insert
<Row>
<LocationCode id="WHD" />
<Color id="BLK" />
<Period id="122012" />
<StockQty id="5" />
</Row>
as last
into (/qty)[1]
')
WHERE ProductCode='AB100'


UPDATE [Product]
SET QtyBalance.modify('
insert
<Row>
<LocationCode id="ddd" />
<Color id="BLK" />
<Period id="122012" />
<StockQty id="10" />
</Row>
as last
into (/qty)[1]
')
WHERE ProductCode='AB250'


UPDATE [Product]
SET QtyBalance.modify('
insert
<Row>
<LocationCode id="WHD" />
<Color id="BLK" />
<Period id="122012" />
<StockQty id="10" />
</Row>
as last
into (/qty)[1]
')
WHERE ProductCode='AB250'

UPDATE Product
SET QtyBalance.modify('
replace value of (/qty/MultiRow[
@color="red"]/@stockQty)[1]
with "20"
')

I want to show something like this

ProductCode | Location | QtyBalance_StockQty |ValueBalance_StockValue
AB250 | ddd | 10 | 10
AB250 | WHD | 10 | 20
AB100 | TED | 10 | 10
AB100 | WHD | 5 | 20
AB100 | WHD | 5 | 20

i dont know how to join this two xml field
Thanks for your reply
Edited by - fungron on 01/28/2013 04:19:23
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 01/28/2013 :  04:58:40  Show Profile  Reply with Quote
see below

use saleDB3
GO

CREATE TABLE #product 
( 
#productCode nchar(10) not null,
Description nvarchar(500),
STDUnitCost numeric(11,4),
STDUnitCostU nvarchar(5),
STDUnitPrice numeric(11,4),
STDUnitPriceU nvarchar(5),
ValueBalance xml DEFAULT '<value />' NOT NULL,
QtyBalance xml DEFAULT '<qty />' NOT NULL
)

INSERT INTO [#product]
([#productCode])
VALUES
('AB250')

INSERT INTO [#product]
([#productCode])
VALUES
('AB100')

UPDATE [#product]
SET ValueBalance.modify('
insert 
<Row>
<LocationCode id="ddd" />
<Period id="122012" />
<StockValue id="10" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE #productCode='AB250'

UPDATE [#product]
SET ValueBalance.modify('
insert 
<Row>
<LocationCode id="WHD" />
<Period id="122012" />
<StockValue id="20" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE #productCode='AB250'


UPDATE [#product]
SET ValueBalance.modify('
insert 
<Row>
<LocationCode id="TED" />
<Period id="122012" />
<StockValue id="10" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE #productCode='AB100'

UPDATE [#product]
SET ValueBalance.modify('
insert 
<Row>
<LocationCode id="WHD" />
<Period id="122012" />
<StockValue id="20" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE #productCode='AB100'


UPDATE [#product]
SET QtyBalance.modify('
insert 
<Row>
<LocationCode id="TED" />
<Color id="RED" />
<Period id="122012" />
<StockQty id="10" />
</Row>
as last
into (/qty)[1]
')
WHERE #productCode='AB100'

UPDATE [#product]
SET QtyBalance.modify('
insert 
<Row>
<LocationCode id="WHD" />
<Color id="RED" />
<Period id="122012" />
<StockQty id="5" />
</Row>
as last
into (/qty)[1]
')
WHERE #productCode='AB100'

UPDATE [#product]
SET QtyBalance.modify('
insert 
<Row>
<LocationCode id="WHD" />
<Color id="BLK" />
<Period id="122012" />
<StockQty id="5" />
</Row>
as last
into (/qty)[1]
')
WHERE #productCode='AB100'


UPDATE [#product]
SET QtyBalance.modify('
insert 
<Row>
<LocationCode id="ddd" />
<Color id="BLK" />
<Period id="122012" />
<StockQty id="10" />
</Row>
as last
into (/qty)[1]
')
WHERE #productCode='AB250'


UPDATE [#product]
SET QtyBalance.modify('
insert 
<Row>
<LocationCode id="WHD" />
<Color id="BLK" />
<Period id="122012" />
<StockQty id="10" />
</Row>
as last
into (/qty)[1]
')
WHERE #productCode='AB250'

UPDATE #product
SET QtyBalance.modify('
replace value of (/qty/MultiRow[
@color="red"]/@stockQty)[1]
with "20"
')



select  p.#productCode,p.Location,q.QtyBalance_StockQty,p.ValueBalance_StockValue
from
(
select 
p.#productCode,
t.u.value('(./LocationCode/@id)[1]','varchar(10)') AS Location,
t.u.value('(./StockValue/@id)[1]','varchar(10)') AS ValueBalance_StockValue
from #product p
cross apply ValueBalance.nodes('/value/Row')t(u)
)p
inner join (select p.#productCode,
m.n.value('(./LocationCode/@id)[1]','varchar(10)') AS Location,
m.n.value('(./StockQty/@id)[1]','varchar(10)') AS QtyBalance_StockQty
from #product p
cross apply QtyBalance.nodes('/qty/Row')m(n)
)q
on q.#productCode = p.#productCode
and q.Location = p.Location


output
----------------------------------------------------------------------------------
#productCode	Location	QtyBalance_StockQty	ValueBalance_StockValue
----------------------------------------------------------------------------------
AB250     	ddd	10	10
AB250     	WHD	10	20
AB100     	TED	10	10
AB100     	WHD	5	20
AB100     	WHD	5	20


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000