export in xml from a sql server table

  • Hello everybody,

    I need to export the records of a table in xml format.

    create table ##prova

    ( Valuta varchar(2),

    Misura float

    )

    insert into ##prova values ('EU',1000)

    insert into ##prova values ('$',2000)

    The final result must be something like this:

    <root>

    <obs id=”0”>

    <dim name=”Valuta” value=”EU” />

    <dim name=”Misura” value=”1000” />

    </obs>

    <obs id=”0”>

    <dim name=”Valuta” value=”$” />

    <dim name=”Misura” value=”2000” />

    </obs>

    </root>

    Thank you

  • This should get you started, notice I changed the data type of the monetary value, float is not the appropriate type!

    😎

    USE tempdb;

    GO

    create table #prova

    ( Valuta varchar(2),

    Misura DECIMAL(18,5)

    )

    insert into #prova values ('EU',1000)

    insert into #prova values ('$',2000)

    /* Adding a row number */

    ;WITH DIM_BASE AS

    (

    SELECT

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RID

    ,PX.Misura

    ,PX.Valuta

    FROM #prova PX

    )

    /* Splitting each column in two attributes */

    ,DIM_DET AS

    (

    SELECT

    D.RID

    ,'Valuta' AS [name]

    ,D.Valuta AS [value]

    FROM DIM_BASE D

    UNION ALL

    SELECT

    D.RID

    ,'Misura' AS [name]

    ,CAST(D.Misura AS VARCHAR(20)) AS [value]

    FROM DIM_BASE D

    )

    /* Join the sub-elements on the row number */

    SELECT

    DB.RID AS '@id'

    ,(SELECT

    DS.name AS 'dim/@name'

    ,DS.value AS 'dim/@value'

    FROM DIM_DET DS

    WHERE DS.RID = DB.RID

    FOR XML PATH(''), TYPE)

    FROM DIM_BASE DB

    FOR XML PATH('obs'), TYPE, ROOT('root')

    DROP TABLE #prova

    Results

    <root>

    <obs id="1">

    <dim name="Valuta" value="EU" />

    <dim name="Misura" value="1000.00000" />

    </obs>

    <obs id="2">

    <dim name="Valuta" value="$" />

    <dim name="Misura" value="2000.00000" />

    </obs>

    </root>

  • thank you very much.

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

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