Post reply

extraction of an query with sql for xml path to xml file

  • September 17, 2010 at 4:26 am

    #141935

    Hi,

    I use SQL SERVER 2005,

    I want to extract query result using "for xml path (''), type" to a xml file .

    my query :

    declare @metier varchar (25)

    declare xx cursor for

    select distinct Perimetre

    From Base..table

    openxx

    fetchxx

    into@metier

    while @@fetch_status = 0

    begin

    SELECT A.Perimetre as "@perimetre",(select convert(char(10), getDate(),103)) 'date_creation',(select count(0) from Base..table B where A.Perimetre = B.Perimetre) 'nb_fichier',

    (select Exercice ,

    (SELECT B.Categorie_doc as 'domaine',

    (Select C.Sous_categorie_doc ,

    (SELECT D.Nom_fichier as 'fichier'

    FROM Base..table D

    WHERE D.Perimetre = C.Perimetre

    And D."Exercice"= C."Exercice"

    AND D.Categorie_doc = C.Categorie_doc

    AND D.Sous_categorie_doc = C.Sous_categorie_doc

    group by D.Perimetre,D."Exercice",D.Categorie_doc,D.Sous_categorie_doc, D.Nom_fichier

    FOR XML PATH(''), TYPE)

    From Base..table C

    WHERE C.Perimetre = B.Perimetre

    and C."Exercice" = B."Exercice"

    and C.Categorie_doc = B.Categorie_doc

    Group by C.Perimetre,C."Exercice", C.Categorie_doc, C.Sous_categorie_doc

    FOR XML PATH(''), TYPE)

    FROM Base..table B

    WHERE E.Perimetre = B.Perimetre

    and B."Exercice" = E."Exercice"

    group by B.Perimetre,B."Exercice",B.Categorie_doc

    FOR XML PATH(''), TYPE)

    FROM Base..table E

    WHERE A.Perimetre = E.Perimetre

    group by E.Perimetre,E."Exercice"

    For xml path ('description'), type )

    FROM Base..table A

    where A.Perimetre = @metier

    group by A.Perimetre

    FOR XML PATH('pack'),type

    fetchxx

    into@metier

    end

    close xx

    deallocate xx

    I don't have xml shema.xsd

    I try with "bcp" but i have many error.

    Do you have an idea ?

    Thank you !

  • September 28, 2010 at 8:33 am

    #1228414

    bcp queryout xml file

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

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