cross apply xml variable with table

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 2008 Forums

Transact-SQL (2008)

cross apply xml variable with table

Author

Topic

dilipd006
Starting Member

4 Posts

Posted - 2010-10-19 : 06:01:11

--hi problem in getting the desired result problem is explained below
--first table
CREATE TABLE RolePrivilege(
[RolePrivilegeID] [int] IDENTITY(1,1) NOT NULL,
[RoleConsumerID] [int] NOT NULL,
[UrlID] [int] NOT NULL,
[Read] [bit] NOT NULL,
[Write] [bit] NOT NULL,
[Critical] [bit] NOT NULL,
CONSTRAINT [PK_RolePrivilege] PRIMARY KEY CLUSTERED
(
[RolePrivilegeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--second table
CREATE TABLE RolePrivilegeDefault(
RolePrivilegeDefaultID int not null,
UrlID int not null,
[Read] bit not null,
Write bit not null,
Critical bit not null,
RoleID smallint not null,
CONSTRAINT PK_RolePrivilegeDefaultI PRIMARY KEY RolePrivilegeDefaultID
)

--values
INSERT INTO RolePrivilegeDefault SELECT '1','1','1','1','1','2'
UNION ALL SELECT '2','2','1','1','1','2'
UNION ALL SELECT '3','1','1','0','0','3'
UNION ALL SELECT '4','2','0','0','0','3'

DECLARE @RoleConsumerTemp table (ID int)--temporary table
--values
INSERT INTO @RoleConsumerTemp(ID) SELECT '1' UNION ALL SELECT '2'

DECLARE @xmlRoleConsumerTemp xml

SET @xmlRoleConsumerTemp = (
SELECT ID
from @RoleConsumerTemp
FOR XML PATH(''),ROOT('RoleConsumerTemp'), TYPE)

INSERT INTO [dbo].[tbl_OTM_WS_RolePrivilege]
(
RoleConsumerID
,UrlID
,[Read]
,Write
,Critical
)
SELECT
V.y.value('(.//ID)[1]','int')
,RPV.UrlID
,RPV.[Read]
,RPV.Write
,RPV.Critical
FROM @xmlRoleConsumerTemp.nodes('/RoleConsumerTemp/ID') AS V(y)
CROSS APPLY
[dbo].[tbl_OTM_WS_RolePrivilegeDefault] RPV

-- get this result while inserting
RolePrivilegeID RoleConsumerID UrlID Read Write Critical
1 1 1 1 1 1
2 1 2 1 1 1
3 1 1 1 0 0
4 1 2 0 0 0
5 2 1 1 1 1
6 2 2 1 1 1
7 2 1 1 0 0
8 2 2 0 0 0

--want this result

RolePrivilegeID RoleConsumerID UrlID Read Write Critical
1 1 1 1 1 1
2 1 2 1 1 1
3 2 1 1 0 0
4 2 2 0 0 0

Sachin.Nand

2937 Posts

Posted - 2010-10-19 : 07:03:06

quote:

INSERT INTO [dbo].[tbl_OTM_WS_RolePrivilege]
(
RoleConsumerID
,UrlID
,[Read]
,Write
,Critical
)
SELECT
V.y.value('(.//ID)[1]','int')
,RPV.UrlID
,RPV.[Read]
,RPV.Write
,RPV.Critical
FROM @xmlRoleConsumerTemp.nodes('/RoleConsumerTemp~~/ID~~') AS V(y)
CROSS APPLY
[dbo].[tbl_OTM_WS_RolePrivilegeDefault] RPV

Remove the red part marked above from the query.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources