Post reply

FOR XML clause - RAW / PATH / AUTO / EXPLICIT

  • February 10, 2011 at 3:47 am

    #231273

    Hello sequel citizens,

    A day before yesterday I realized the scope of XML we have. I was working on query where I need to return the list of tables with columns & index information in XML format something like ...

    <Table Name="XYZ" >

    <Column Name = "Col1" />

    <Column Name = "Col2" />

    <Column Name = "Col3" />

    </TableName>

    <Table Name="ABC" >

    <Column Name = "Xol1" />

    <Column Name = "Xol2" />

    <Column Name = "Xol3" />

    </TableName>.....

    Similar information will be return for index details in XML. somewhere I did accomplished the requirement by writing below query using EXPLICIT for xml clause

    select 1 as tag, 0 as parent, object_name (object_id) as [indexsummary!1!tablename], null as [indexsummary!2!indexname] from sys.tables

    union all

    select2 as tag, 1 as parent, object_name (object_id), name from sys.indexes where object_id in(select object_id from sys.tables)

    order by [indexsummary!1!tablename], [indexsummary!2!indexname]

    for xml explicit

    but I want to know is there is any other way :Whistling: to get the information using other FOR XML clause??

    Thanks in advance for your suggestions.

    Abhijit - http://abhijitmore.wordpress.com

  • February 10, 2011 at 12:59 pm

    #1285437

    Something like this?

    SELECT

    st.name AS '@name',

    si.name AS 'index/@name'

    FROM sys.indexes si

    INNER JOIN sys.tables st ON si.object_id = st.object_id

    FOR XML PATH('table')


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

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