/*
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]'
Friday, August 27, 2010
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?
*/
--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?
*/
Subscribe to:
Posts (Atom)


