Question about sql xml data type select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question about sql xml data type select statement

Author

Topic

fungron
Starting Member

5 Posts

Posted - 2013-01-26 : 03:31:17

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-26 : 04:20:10

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/

fungron
Starting Member

5 Posts

Posted - 2013-01-26 : 05:51:33

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-26 : 05:56:07

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/

fungron
Starting Member

5 Posts

Posted - 2013-01-28 : 04:16:14

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-28 : 04:58:40

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources