Friday, August 27, 2010

Parse Hierarchy(TreeView) XML using Outer Apply

/*

This sample will help you to parse Tree View XMl of Explicit format.
If you Apply Cross Apply on for Outer Apply then it will not display the child which does not have 3 Levl

If you store this XML in to temp table then you can update the Parent child clasification in to table.

*/
-- Please Replace [ and ] by < and >
DECLARE @HierarchyDetail XML

SET @HierarchyDetail =
'[treeview]


[treeview-nodes]

[treeview-node node-text="Account -1001" node-value="1001"]

[treeview-nodes]

[treeview-node node-text="Account -1075" node-value="1075"]

[treeview-nodes]

[treeview-node node-text="Account - 1132" node-value="1132" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[treeview-node node-text="Account - 1000" node-value="1000"]

[treeview-nodes]

[treeview-node node-text="Account -1077" node-value="1077"]

[treeview-nodes]

[treeview-node node-text="Account -1134" node-value="1134" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[treeview-node node-text="Account - 1080" node-value="1080"]

[treeview-nodes]

[treeview-node node-text="Account - 1101 " node-value="1101"]

[treeview-nodes]

[treeview-node node-text="Account - 1135" node-value="1135" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[treeview-node node-text="Account - 1114" node-value="1114"]

[treeview-nodes]

[treeview-node node-text="Account - 1124 " node-value="1124"]

[treeview-nodes]

[treeview-node node-text="Account - 1137" node-value="1137" /]

[treeview-node node-text="Account - 2937 " node-value="2937" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview]'

Thursday, July 22, 2010

Select Rows from Table1 NOT IN Table2 (t1.a = t2.a and t1.b = t2.b)

--Source(Table1 & Table2)













--Expected OutPut





DECLARE @T1 TABLE(A INT, B INT,C VARCHAR(10))
DECLARE @T2 TABLE(A INT, B INT,C VARCHAR(10))
INSERT INTO @T1(A,B,C)
VALUES (1,1,'SS1')
,(1,2,'SS2')
,(1,3,'SS3')


INSERT INTO @T2(A,B,C)
VALUES (1,1,'SS1')
,(1,2,'SS2')


--Method1:
SELECT A,B
FROM @T1
EXCEPT
SELECT A,B
FROM @T2
--Method2:
SELECT A.A,A.B
FROM @T1 A
left JOIN @T2 B ON A.A = B.A
AND a.B = b.B
WHERE b.B is null
--Method3:
SELECT * FROM @T1 AS T1
WHERE NOT EXISTS (SELECT 0
FROM @T2 AS T2
WHERE T2.A = T1.A
AND T2.B = T1.B
)
 
/*
 
I hope these will give the expected Result, Now i am looking over this which is best?
 
*/