Cross apply and inner join together on xml type

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)

Cross apply and inner join together on xml type

Author

Topic

sujithukvl@gmail.com
Starting Member

22 Posts

Posted - 2011-04-27 : 02:44:30

I am getting some ’suggestions’ nodes as in an xml.

I want to put them in to @DATABASETABLE (originally this should be a table in databse)

See the query blelow.

Can I mix point1 and point2 ?

And thus I can avoid using at least one table variable(eg @InsertedTagSuggestions)

DECLARE @DATABASETABLE TABLE (
[Id] INT ,
LevelId INT,
[Name] VARCHAR(400)
)

DECLARE @Tag TABLE (
--[Id] INT IDENTITY(1,1),
[Name] VARCHAR(400),
Levelid INT,
Testdata xml)

DECLARE @TagCollection xml

set @TagCollection =N'<tag enableTagging="false" level="3">
<name>Tag 4</name>
<suggestions>
<suggestion>
<description>1 OF 4 </description>
</suggestion>
<suggestion>
<description>2 OF 4. </description>
</suggestion>
</suggestions>
</tag>
<tag enableTagging="false" level="3" >
<name>Tag 5</name>
<suggestions>
<suggestion>
<description>1 OF 5 </description>
</suggestion>
<suggestion>
<description>2 OF 5. </description>
</suggestion>
</suggestions>
</tag>'

DECLARE @CurrentLevelId INT
INSERT INTO @Tag
SELECT
T.col.value('name[1]','varchar(400)') AS [Name],
T.col.value('@level','int') AS LevelId,
T.col.query('suggestions') AS TestData
FROM @TagCollection.nodes('//tag') AS T(col)

--select * from @Tag
SET @CurrentLevelId = 1

WHILE(@CurrentLevelId <= 3)
BEGIN

DECLARE @InsertedTag TABLE (
[Id] INT,
[Name] VARCHAR(400),
LevelId INT
)

INSERT INTO @DATABASETABLE
( [Id] , [Name] , LevelId )
OUTPUT INSERTED.Id, INSERTED.[Name], @CurrentLevelId INTO @InsertedTag
SELECT Levelid,
[Name],
@CurrentLevelId

FROM @Tag T
WHERE LevelId = @CurrentLevelId

DECLARE @InsertedTagSuggestions TABLE (
[Id] INT,
[SuggestionDescription] xml
)

----------------point1-----------------------------
INSERT INTO @InsertedTagSuggestions
SELECT T2.Id,T.TestData FROM @InsertedTag T2
INNER JOIN @Tag T ON T.LevelId=T2.LevelId
---------------------------------------------

----------------point 2------------------------------
DECLARE @Suggestions TABLE (
[SortOrder] INT IDENTITY(1,1),
[TagId] INT,
[Description] varchar(max)
)

DELETE FROM @Suggestions
INSERT INTO @Suggestions
SELECT
Id,
Suggestion.value('(description)[1]', 'varchar(50)') AS 'Description'
FROM
@InsertedTagSuggestions
CROSS APPLY
[SuggestionDescription].nodes('/suggestions/suggestion') AS Node(Suggestion)
-----------------------------------------------

--select * from @InsertedTagSuggestions O

SET @CurrentLevelId = @CurrentLevelId +1
END

--SELECT * FROM @DATABASETABLE
SELECT* FROM @Suggestions

see the above query works stand alone :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources