I have an XML hierarchy like this in an XML type column of a table with 10,000 records-
<Root>
<Elem1>
<Parent1>
<Separator>
<Child1/>
</Separator>
</Parent1>
</Elem1>
</Root>
I have a query like this -
DECLARE @Root VARCHAR(50)
DECLARE @Entity VARCHAR(50)
DECLARE @ParentNode VARCHAR(50)
DECLARE @Separator VARCHAR(50)
DECLARE @ChildNode VARCHAR(50)
SET @Root = 'Root'
SET @Entity = 'Elem1'
SET @ParentNode = 'Parent1'
SET @Separator = 'separator'
SET @ChildNode = 'Child1'
select Parent.P.value('.', 'varchar(max)') as MyValue,
T.uniqueId, T.XMLCol
from [XMLTable] as T
cross apply
(SELECT
XMLTable.XMLCol.query('(/*[local-name()=sql:variable("@Root")]/*[local-name(.)=sql:variable("@Entity")]/*[local-name(.)=sql:variable("@ParentNode")]/*[local-name(.)=sql:variable("@Separator")]/*[local-name(.)=sql:variable("@ChildNode")])[1]'
) as Parent(P)
How can I further Optimize this query. Currently, it is taking 2 secs and if I do further INNER JOINS, it adds up to the time. I tried creating a PRIMARY Index on the XML Column, but it takes more time !
EDIT- If I hardcode the path instead of using variables, then it takes less than a second. But, I want it in a Table-Valued function and cannot hardcode path ?
EDIT - Solution
select x.value('(Parent1/Separator1/Child1)[1]', 'varchar(max)') as Col1,
x.value('(Parent2/Separator2/Child2)[1]', 'varchar(max)') as Col2,
x.value('(Parent3)[1]', 'varchar(max)') as Col3
from [XMLTable] T
cross apply T.XMLCOL.nodes('/Root/Elem1') a(x)
The above query takes just about one sec. It seems to be the fastest of all. So, instead of going for a Table valued function with params, the above query can be dynamically prepared and executed on the fly using ADO.NET capabilities
Correct me please ... ?