I have a stored procedure which is selecting some 1-n relations and related data to the referenced column as XML data.
The purpose is to return a record and it's 1-n relations as ONE record with extra data columns, this is done using adding these related data as XML.
Reference table: (TABLE A)
ID NAME VALUE
---------------------
1 Sepehr 1000
2 Sarah 1001
Related table: (TABLE B)
ID Value FK_Value ORDER TITLE
-------------------------------------
1 A 1000 1 t1
2 B 1000 2 t2
3 C 1000 3 t3
I want to get this output:
ID NAME FK_Value Attribs
-----------------------------------------------------
1 Sepehr 1000 <XML><ID>1</ID><ID>2</ID><ID>3</ID></XML>
2 Sarah 1001 null
Actually I was hoping to create a view to do this, but I couldn't and someone told me its not possible using views.
Finally this is the stored procedure I have written - is this a correct approach or are there any other ways?
DECLARE @T1 table (A_ID int,Attribs XML)
DECLARE db_cursorLegendRowsValues CURSOR FOR
SELECT ID, VALUE
FROM A
OPEN db_cursorLegendRowsValues
FETCH NEXT FROM db_cursorLegendRowsValues INTO @loop_ID, @loop_VALUE
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE db_cursorInternal CURSOR FOR
SELECT TITLE, ORDER
FROM B
WHERE FK_Value = @loop_VALUE
OPEN db_cursorInternal
FETCH NEXT FROM db_cursorInternal INTO @tmpTitle, @ORDER
WHILE @@FETCH_STATUS = 0
BEGIN
SET @querySelect = @querySelect + ', MAX(CASE WHEN order = ' + cast(@ORDER as nvarchar(max)) + ' THEN value END) AS [' +REPLACE (@tmpTitle,' ','_') + '] '
FETCH NEXT FROM db_cursorInternal INTO @tmpTitle, @ORDER
END
CLOSE db_cursorInternal
DEALLOCATE db_cursorInternal
SET @query =
' SELECT ' + cast(@loop_ID as nvarchar(max)) +',(
SELECT A.Value,
'
SET @query = @query + STUFF(@querySelect,1,1,'') + ' FROM A
WHERE [A.Value] = ' + cast(@loop_VALUE as nvarchar(max)) + '
FOR XML RAW (''Item''), root (''Items'') , ELEMENTS XSINIL )'
SET @querySelect = ''
--PRINT(@query)
INSERT into @T1 execute (@query )
FETCH NEXT FROM db_cursorLegendRowsValues INTO @loop_ID, @loop_VALUE
END
CLOSE db_cursorLegendRowsValues
DEALLOCATE db_cursorLegendRowsValues
SELECT * FROM @T1
The whole lot can be condensed to a few lines, no cursors needed at all. This wil be useable in a VIEW:
DECLARE @tblA TABLE(ID INT IDENTITY,NAME VARCHAR(100),VALUE INT);
INSERT INTO @tblA VALUES
('Sepehr',1000)
,('Sarah',1001);
DECLARE @tblB TABLE(ID INT IDENTITY,Value VARCHAR(100),FK_Value INT,[ORDER] INT,TITLE VARCHAR(100));
INSERT INTO @tblB VALUES
('A',1000,1,'t1')
,('B',1000,2,'t2')
,('C',1000,3,'t3');
SELECT a.*
,(SELECT ID FROM @tblB AS b WHERE b.FK_Value=a.VALUE FOR XML PATH(''),ROOT('XML'),TYPE) AS Attribs
FROM @tblA AS a
The result
ID NAME VALUE Attribs
1 Sepehr 1000 <XML><ID>1</ID><ID>2</ID><ID>3</ID></XML>
2 Sarah 1001 NULL