XML Fields within a View

  • We have a small app that currently uses the xml field for data collection. For reporting purposes the xml has been flattened out into a view, so that the report writer can query the view as normal instead of writing the xml explosion in the data source.

    Here is the question. Say for instance you have 10000 records, does the view explode all of the xml then apply the wear clause from the select statement, or does it just explode the xml for the rows matching the where statement?

    Note the field used in the where statement is not in the xml but a normal field in the storage table.

    ex table:

    Id, firstname, lastname, xmlstoragefield

    Query's where clause hits the "ID" field"

    Thanks,

    (note I have tested this for 10000 records (small dataset I now) and it seems to have the same performance as when it had 20 records). Any insight into the arcane of the optimizer would be greatly appreciated.

  • Try creating an xml index on the fields containing xml data type. You can create Primary XML index and add secondary XML indexes

    Albert B. Matubis
    Database Administrator: Trend Micro PH
    MCP. MCAD. MCDBA. MCT
    MCTS: SQL Server 2005
    MCTS: Business Intelligence
    MCITP: Database Administrator
    MCITP: Business Intelligence Applications Developer

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply