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.
Author |
Topic |
fungron
Starting Member
5 Posts |
Posted - 2013-01-26 : 03:31:17
|
This is my table structureUSE saleDB2;GOCREATE 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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/value)[1]')WHERE ProductCode='AB100'UPDATE ProductSET 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 XMLSELECT ValueBalance.value('(/value/Row/LocationCode/@id)[1]','nvarchar(max)') AS LocationFROM ProductI 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 belowCREATE 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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/value)[1]')WHERE ProductCode='AB100'UPDATE #ProductSET QtyBalance.modify('replace value of (/qty/MultiRow[@color="red"]/@stockQty)[1]with "20"')SELECT t.u.value('./@id[1]','varchar(10)') from #Product pCROSS APPLY ValueBalance.nodes('/value/Row/LocationCode')t(u)DROP TABLE #Productoutput--------------------------TEDWHDTEDWHD ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
fungron
Starting Member
5 Posts |
Posted - 2013-01-26 : 05:51:33
|
Thanks for your helpi don't know CROSS APPLY statement before.Thanks for your teching |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
fungron
Starting Member
5 Posts |
Posted - 2013-01-28 : 04:16:14
|
Sorry, i have question again, also xml select statementuse saleDB3GOCREATE 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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/qty)[1]')WHERE ProductCode='AB250'UPDATE ProductSET QtyBalance.modify('replace value of (/qty/MultiRow[@color="red"]/@stockQty)[1]with "20"')I want to show something like thisProductCode | Location | QtyBalance_StockQty |ValueBalance_StockValueAB250 | ddd | 10 | 10AB250 | WHD | 10 | 20AB100 | TED | 10 | 10AB100 | WHD | 5 | 20AB100 | WHD | 5 | 20i dont know how to join this two xml fieldThanks for your reply |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-28 : 04:58:40
|
see belowuse saleDB3GOCREATE 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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/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 lastinto (/qty)[1]')WHERE #productCode='AB250'UPDATE #productSET QtyBalance.modify('replace value of (/qty/MultiRow[@color="red"]/@stockQty)[1]with "20"')select p.#productCode,p.Location,q.QtyBalance_StockQty,p.ValueBalance_StockValuefrom(select p.#productCode,t.u.value('(./LocationCode/@id)[1]','varchar(10)') AS Location,t.u.value('(./StockValue/@id)[1]','varchar(10)') AS ValueBalance_StockValuefrom #product pcross apply ValueBalance.nodes('/value/Row')t(u))pinner join (select p.#productCode,m.n.value('(./LocationCode/@id)[1]','varchar(10)') AS Location,m.n.value('(./StockQty/@id)[1]','varchar(10)') AS QtyBalance_StockQtyfrom #product pcross apply QtyBalance.nodes('/qty/Row')m(n))qon q.#productCode = p.#productCodeand q.Location = p.Locationoutput----------------------------------------------------------------------------------#productCode Location QtyBalance_StockQty ValueBalance_StockValue----------------------------------------------------------------------------------AB250 ddd 10 10AB250 WHD 10 20AB100 TED 10 10AB100 WHD 5 20AB100 WHD 5 20 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|