Need to create index on the xml table

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 2008 Forums

SQL Server Administration (2008)

Need to create index on the xml table

Author

Topic

Ajdba
Starting Member

3 Posts

Posted - 2012-08-07 : 14:55:56

Hello,
I am getting the years as an in parameter as following but in my execution plan it is showing 99% cost;

SELECT CAST(Node.query('text()') AS varchar(100)) as YEAR
FROM @XMLSelectedYears.nodes('/Years/*/YYYY') tempxml (Node)) a

How can I create primary index on the table which is passed parameter; I am pretty new in this. Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-07 : 15:09:19

You cannot create an XML index on a variable. I would try the following:

a) If at all possible, avoid the wild-card in the path - instead use the exact path. '/Years/NodeA/NodeB/YYYY'

b) If that does not help much, create a table with an XML index, insert the XML into that table and then run the query against the table. However, if you are querying just once, the cost will not be worth it. Creating the XML index is an expensive operation because it shreds the XML. XML indexes also takes up a lot of space.

Ajdba
Starting Member

3 Posts

Posted - 2012-08-08 : 09:21:51

If I use the exact path like '/Years/NodeA/NodeB/YYYY' instead of wild-card, how would you rewrite the above pasted query.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-08 : 09:36:37

if that's all that's being passed in (a list of years...) there are better options than XML.

Maybe read here:
http://www.sommarskog.se/arrays-in-sql-2008.html

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 09:37:40

It really depends on your XML structure. In the two examples below, in the first case you are forced to use the wildcard. In the second case, you can use exact path.

DECLARE @XMLSelectedYears XML;

SET @XMLSelectedYears =
'<Years>
  <Obama>
  <YYYY>2009</YYYY>
  </Obama>
  <Bush>
  <YYYY>2001</YYYY>
  </Bush>
  <Clinton>
  <YYYY>1993</YYYY>
  </Clinton>
</Years>';
SELECT CAST(Node.query('text()') AS varchar(100)) as YEAR 
FROM @XMLSelectedYears.nodes('/Years/*/YYYY') tempxml (Node) 

SET @XMLSelectedYears = 
'<Years>
  <StartYear>
  <YYYY>2009</YYYY>
  </StartYear>
  <StartYear>
  <YYYY>2001</YYYY>
  </StartYear>
  <StartYear>
  <YYYY>1993</YYYY>
  </StartYear>
</Years>';
SELECT CAST(Node.query('text()') AS varchar(100)) as YEAR 
FROM @XMLSelectedYears.nodes('/Years/StartYear/YYYY') tempxml (Node);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources