Generate a normalized XML doc from SQL Server

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • muesliflakes
    #1

    Generate a normalized XML doc from SQL Server

    I'm trying to generate a normalized XML document out of SQL server
    that reflects the data
    structure of a table.

    Eg. This is what I would like to get

    <table name='MtFeedbac k'>
    <field name="MtFeedbac kIy" type="int"/>
    <field name="Title" type="varchar" size="50"/>
    <field name="FirstName " type="varchar" size="50"/>
    <field name="Surname" type="varchar" size="50"/>
    <field name="insertedo n" type="datetime"/>
    </table>

    I have tried two ways of and these are the results. The 1st technique
    is close

    select
    1 as Tag,
    null as Parent,
    '' as [table!1],
    null as [field!2!table-name],
    null as [field!2!name],
    null as [field!2!type],
    null as [field!2!size]
    UNION ALL
    select
    2 as Tag,
    1 as Parent,
    null as [table!1],
    i.TABLE_NAME as [field!2!table-name],
    i.COLUMN_NAME as [field!2!name],
    i.DATA_TYPE as [field!2!type],
    i.CHARACTER_MAX IMUM_LENGTH as [field!2!size]
    from
    information_sch ema.columns i
    where
    table_name = 'MtFeedback'
    FOR XML EXPLICIT

    <table>
    <field table-name="MtFeedbac k" name="MtFeedbac kIy" type="int"/>
    <field table-name="MtFeedbac k" name="Title" type="varchar"
    size="50"/>
    <field table-name="MtFeedbac k" name="FirstName " type="varchar"
    size="50"/>
    <field table-name="MtFeedbac k" name="Surname" type="varchar"
    size="50"/>
    <field table-name="MtFeedbac k" name="insertedo n" type="datetime"/>
    </table>

    The 2nd technique fails totally.

    select
    1 as Tag,
    null as Parent,
    i.TABLE_NAME as [table!1!name],
    null as [field!2!name],
    null as [field!2!type],
    null as [field!2!size]
    from
    information_sch ema.columns i
    UNION ALL
    select
    2 as Tag,
    1 as Parent,
    null as [table!1!name],
    i.COLUMN_NAME as [field!2!name],
    i.DATA_TYPE as [field!2!type],
    i.CHARACTER_MAX IMUM_LENGTH as [field!2!size]
    from
    information_sch ema.columns i
    where
    table_name = 'MtFeedback'
    FOR XML EXPLICIT

    <table name="MtFeedbac k"/>
    <table name="MtFeedbac k"/>
    <table name="MtFeedbac k"/>
    <table name="MtFeedbac k"/>
    <table name="MtFeedbac k"/>
    <table name="MtFeedbac k"/>
    <table name="MtFeedbac k"/>
    <table name="MtFeedbac k"/>
    <table name="MtFeedbac k"/>
    <table name="categorie s"/>
    <table name="categorie s"/>
    <table name="products"/>
    <table name="products"/>
    <table name="products"/>
    <table name="descripti ons"/>
    <table name="descripti ons"/>
    <table name="descripti ons"/>
    <table name="syssegmen ts"/>
    <table name="syssegmen ts"/>
    <table name="syssegmen ts"/>
    <table name="sysconstr aints"/>
    <table name="sysconstr aints"/>
    <table name="sysconstr aints"/>
    <table name="sysconstr aints"/>
    <table name="sysconstr aints"/>
    <table name="sysconstr aints"/>
    <table name="sysconstr aints"/>
    <table name="dtpropert ies"/>
    <table name="dtpropert ies"/>
    <table name="dtpropert ies"/>
    <table name="dtpropert ies"/>
    <table name="dtpropert ies"/>
    <table name="dtpropert ies"/>
    <table name="dtpropert ies">
    <field name="MtFeedbac kIy" type="int"/>
    <field name="Title" type="varchar" size="50"/>
    <field name="FirstName " type="varchar" size="50"/>
    <field name="Surname" type="varchar" size="50"/>
    <field name="insertedo n" type="datetime"/>
    </table>

    Cheers Dave
    Tags: None
    • oj
      #2
      Re: Generate a normalized XML doc from SQL Server

      declare @tb sysname
      set @tb='MtFeedback '

      select
      1 as Tag,
      null as Parent,
      @tb as [table!1!name],
      null as [field!2!name],
      null as [field!2!type],
      null as [field!2!size]
      UNION ALL
      select
      2 as Tag,
      1 as Parent,
      i.TABLE_NAME as [table!1!name],
      i.COLUMN_NAME as [field!2!name],
      i.DATA_TYPE as [field!2!type],
      i.CHARACTER_MAX IMUM_LENGTH as [field!2!size]
      from
      information_sch ema.columns i
      where
      table_name = @tb
      FOR XML EXPLICIT


      "muesliflak es" <muesliflakes@y ahoo.com.au> wrote in message
      news:8deb82de.0 406011915.399be 4df@posting.goo gle.com...[color=blue]
      > I'm trying to generate a normalized XML document out of SQL server
      > that reflects the data
      > structure of a table.
      >
      > Eg. This is what I would like to get
      >
      > <table name='MtFeedbac k'>
      > <field name="MtFeedbac kIy" type="int"/>
      > <field name="Title" type="varchar" size="50"/>
      > <field name="FirstName " type="varchar" size="50"/>
      > <field name="Surname" type="varchar" size="50"/>
      > <field name="insertedo n" type="datetime"/>
      > </table>
      >
      > I have tried two ways of and these are the results. The 1st technique
      > is close
      >
      > select
      > 1 as Tag,
      > null as Parent,
      > '' as [table!1],
      > null as [field!2!table-name],
      > null as [field!2!name],
      > null as [field!2!type],
      > null as [field!2!size]
      > UNION ALL
      > select
      > 2 as Tag,
      > 1 as Parent,
      > null as [table!1],
      > i.TABLE_NAME as [field!2!table-name],
      > i.COLUMN_NAME as [field!2!name],
      > i.DATA_TYPE as [field!2!type],
      > i.CHARACTER_MAX IMUM_LENGTH as [field!2!size]
      > from
      > information_sch ema.columns i
      > where
      > table_name = 'MtFeedback'
      > FOR XML EXPLICIT
      >
      > <table>
      > <field table-name="MtFeedbac k" name="MtFeedbac kIy" type="int"/>
      > <field table-name="MtFeedbac k" name="Title" type="varchar"
      > size="50"/>
      > <field table-name="MtFeedbac k" name="FirstName " type="varchar"
      > size="50"/>
      > <field table-name="MtFeedbac k" name="Surname" type="varchar"
      > size="50"/>
      > <field table-name="MtFeedbac k" name="insertedo n" type="datetime"/>
      > </table>
      >
      > The 2nd technique fails totally.
      >
      > select
      > 1 as Tag,
      > null as Parent,
      > i.TABLE_NAME as [table!1!name],
      > null as [field!2!name],
      > null as [field!2!type],
      > null as [field!2!size]
      > from
      > information_sch ema.columns i
      > UNION ALL
      > select
      > 2 as Tag,
      > 1 as Parent,
      > null as [table!1!name],
      > i.COLUMN_NAME as [field!2!name],
      > i.DATA_TYPE as [field!2!type],
      > i.CHARACTER_MAX IMUM_LENGTH as [field!2!size]
      > from
      > information_sch ema.columns i
      > where
      > table_name = 'MtFeedback'
      > FOR XML EXPLICIT
      >
      > <table name="MtFeedbac k"/>
      > <table name="MtFeedbac k"/>
      > <table name="MtFeedbac k"/>
      > <table name="MtFeedbac k"/>
      > <table name="MtFeedbac k"/>
      > <table name="MtFeedbac k"/>
      > <table name="MtFeedbac k"/>
      > <table name="MtFeedbac k"/>
      > <table name="MtFeedbac k"/>
      > <table name="categorie s"/>
      > <table name="categorie s"/>
      > <table name="products"/>
      > <table name="products"/>
      > <table name="products"/>
      > <table name="descripti ons"/>
      > <table name="descripti ons"/>
      > <table name="descripti ons"/>
      > <table name="syssegmen ts"/>
      > <table name="syssegmen ts"/>
      > <table name="syssegmen ts"/>
      > <table name="sysconstr aints"/>
      > <table name="sysconstr aints"/>
      > <table name="sysconstr aints"/>
      > <table name="sysconstr aints"/>
      > <table name="sysconstr aints"/>
      > <table name="sysconstr aints"/>
      > <table name="sysconstr aints"/>
      > <table name="dtpropert ies"/>
      > <table name="dtpropert ies"/>
      > <table name="dtpropert ies"/>
      > <table name="dtpropert ies"/>
      > <table name="dtpropert ies"/>
      > <table name="dtpropert ies"/>
      > <table name="dtpropert ies">
      > <field name="MtFeedbac kIy" type="int"/>
      > <field name="Title" type="varchar" size="50"/>
      > <field name="FirstName " type="varchar" size="50"/>
      > <field name="Surname" type="varchar" size="50"/>
      > <field name="insertedo n" type="datetime"/>
      > </table>
      >
      > Cheers Dave[/color]


        Comment

        Previous template Next
        Working...