Need help using STUFF and XML Path

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help using STUFF and XML Path

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-02-04 : 03:07:00

My table as follow
/***/
CREATE TABLE [dbo].[priceINET](
[RouteID] [varchar](10) NULL,
[SCout] [varchar](20) NULL,
[ECout] [varchar](20) NULL,
[Status] [varchar](1) NULL,
[CurrencyName] [varchar](10) NULL,
[APrice] [decimal](8, 2) NULL,
[CPrice] [decimal](8, 2) NULL,
[AAPrice] [decimal](8, 2) NULL,
[ACPrice] [decimal](8, 2) NULL,
[RAPrice] [decimal](8, 2) NULL,
[RCPrice] [decimal](8, 2) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[priceSAM](
[RouteID] [varchar](10) NULL,
[SCout] [varchar](20) NULL,
[ECout] [varchar](20) NULL,
[Status] [varchar](1) NULL,
[CurrencyName] [varchar](10) NULL,
[APrice] [decimal](8, 2) NULL,
[CPrice] [decimal](8, 2) NULL,
[AAPrice] [decimal](8, 2) NULL,
[ACPrice] [decimal](8, 2) NULL,
[RAPrice] [decimal](8, 2) NULL,
[RCPrice] [decimal](8, 2) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[priceYAP](
[RouteID] [varchar](10) NULL,
[SCout] [varchar](20) NULL,
[ECout] [varchar](20) NULL,
[Status] [varchar](1) NULL,
[CurrencyName] [varchar](10) NULL,
[APrice] [decimal](8, 2) NULL,
[CPrice] [decimal](8, 2) NULL,
[AAPrice] [decimal](8, 2) NULL,
[ACPrice] [decimal](8, 2) NULL,
[RAPrice] [decimal](8, 2) NULL,
[RCPrice] [decimal](8, 2) NULL
) ON [PRIMARY]
/***/

/*So far, i was created Dynamic SQL as follow,*/
declare @sql varchar(8000)
SELECT @sql = STUFF(
(
SELECT ' SELECT ''???'',[RouteID],[SCout],[ECout],[Status],[CurrencyName],[APrice],[CPrice],[AAPrice],[ACPrice],[RAPrice],[RCPrice] FROM dbo.' AS "text()",
[name] AS "text()",
' UNION ALL '
FROM sys.tables
WHERE [name] LIKE 'price%'
FOR XML PATH('')
),1,1, '')

-- Remove the final UNION ALL
SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 10)
print @sql

/*The return as follow*/
SELECT '???',[RouteID],[SCout],[ECout],[Status],[CurrencyName],[APrice],[CPrice],[AAPrice],[ACPrice],[RAPrice],[RCPrice] FROM dbo.priceINET UNION ALL SELECT '???',[RouteID],[SCout],[ECout],[Status],[CurrencyName],[APrice],[CPrice],[AAPrice],[ACPrice],[RAPrice],[RCPrice] FROM dbo.priceSAM UNION ALL SELECT '???',[RouteID],[SCout],[ECout],[Status],[CurrencyName],[APrice],[CPrice],[AAPrice],[ACPrice],[RAPrice],[RCPrice] FROM dbo.priceYAP

My question is How to replace ??? with table Name? So the result as follow,
SELECT 'priceINET',[RouteID],[SCout],[ECout],[Status],[CurrencyName],[APrice],[CPrice],[AAPrice],[ACPrice],[RAPrice],[RCPrice] FROM dbo.priceINET UNION ALL SELECT 'priceSAM',[RouteID],[SCout],[ECout],[Status],[CurrencyName],[APrice],[CPrice],[AAPrice],[ACPrice],[RAPrice],[RCPrice] FROM dbo.priceSAM UNION ALL SELECT 'priceYAP',[RouteID],[SCout],[ECout],[Status],[CurrencyName],[APrice],[CPrice],[AAPrice],[ACPrice],[RAPrice],[RCPrice] FROM dbo.priceYAP

Need help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 03:12:52

just replace ??? by name field from sys.tables above

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-02-04 : 03:22:06

quote:
Originally posted by visakh16

just replace ??? by name field from sys.tables above

Yesss....
Now my code as follow,
declare @sql varchar(8000)
SELECT @sql = STUFF(
(
SELECT ' SELECT ' + [name] + ',[RouteID],[SCout],[ECout],[Status],[CurrencyName],[APrice],[CPrice],[AAPrice],[ACPrice],[RAPrice],[RCPrice] FROM dbo.' AS "text()",
[name] AS "text()",
' UNION ALL '
FROM sys.tables
WHERE [name] LIKE 'price%'
FOR XML PATH('')
),1,1, '')

-- Remove the final UNION ALL
SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 10)
print @sql

2nd Question Sir,
[Name] will return as follow
How to trim the [Name]. Let's say the value as follow,

Origin
[Name]
------------
priceINET
priceSAM
priceYAP

New Value
[Name]
------------
INET
SAM
YAP

How to adjust my,
declare @sql varchar(8000)
SELECT @sql = STUFF(
(
SELECT ' SELECT ' + [name] + ',[RouteID],[SCout],[ECout],[Status],[CurrencyName],[APrice],[CPrice],[AAPrice],[ACPrice],[RAPrice],[RCPrice] FROM dbo.' AS "text()",
[name] AS "text()",
' UNION ALL '
FROM sys.tables
WHERE [name] LIKE 'price%'
FOR XML PATH('')
),1,1, '')

-- Remove the final UNION ALL
SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 10)
print @sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 03:25:36

change [name] to REPLACE([name].'price','')

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-02-04 : 03:29:00

quote:
Originally posted by visakh16

change [name] to REPLACE([name].'price','')

declare @sql varchar(8000)
SELECT @sql = STUFF(
(
SELECT ' SELECT ' + REPLACE([name].'price','') + ',[RouteID],[SCout],[ECout],[Status],[CurrencyName],[APrice],[CPrice],[AAPrice],[ACPrice],[RAPrice],[RCPrice] FROM dbo.' AS "text()",
[name] AS "text()",
' UNION ALL '
FROM sys.tables
WHERE [name] LIKE 'price%'
FOR XML PATH('')
),1,1, '')

-- Remove the final UNION ALL
SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 10)
print @sql

The return as follow,
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'price'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 03:34:30

quote:
Originally posted by wkm1925

quote:
Originally posted by visakh16

change [name] to REPLACE([name],'price','')

declare @sql varchar(8000)
SELECT @sql = STUFF(
(
SELECT ' SELECT ' + REPLACE([name],'price','') + ',[RouteID],[SCout],[ECout],[Status],[CurrencyName],[APrice],[CPrice],[AAPrice],[ACPrice],[RAPrice],[RCPrice] FROM dbo.' AS "text()",
[name] AS "text()",
' UNION ALL '
FROM sys.tables
WHERE [name] LIKE 'price%'
FOR XML PATH('')
),1,1, '')

-- Remove the final UNION ALL
SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 10)
print @sql

The return as follow,
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'price'.

that was a typo
it should be , not .

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-02-04 : 05:58:18

tq sir

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 06:00:52

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources