Can't manage to export multiple rows from database to XML

I have a table with 3 rows, 1 is being used as row identifier, 2nd is used to make a relation with other table, and 3rd holds content. I’m doing a query where the the given result is kind of what I need but has a little mistake.

Table:print

  • SQL Call Stored Procedure for each Row without using a cursor
  • Checking if a SQL Server login already exists
  • How do I create a multiple column unique constraint in SQL Server
  • DateTime.MinValue vs new DateTime() in C#
  • Find query that fired a trigger
  • sum result is being multiplied by number of rows
  • Here’s the query that I’m using:

    SELECT Plate, tbl_veiculos.ID, Section, Category, Brand, Model, Version, Fuel, Price, B2BPrice, Year, Month, TollClass, Origin, Color, SeatColour, Seats, Kms, Doors, HP, Owners, CC, Obs, TaxDeductible, WarrantyMonth,
    (SELECT Name, Street, Locality, Email from tbl_AdStand where tbl_AdStand.ID = tbl_veiculos.ID AND tbl_veiculos.ID = 1 FOR XML PATH('AdStand'), type ), 
    (SELECT DISTINCT Site from tbl_ExportSites where tbl_ExportSites.ID = tbl_veiculos.ID AND tbl_veiculos.ID = 1 FOR XML PATH('ExportSites'), type )
    FROM tbl_veiculos, tbl_veiculo_spec, tbl_AdStand
    WHERE tbl_veiculos.ID = tbl_veiculo_spec.ID AND tbl_AdStand.ID = tbl_veiculos.ID AND tbl_veiculos.ID = 1
    FOR XML PATH ('Vehicle'), TYPE, ROOT('VehicleList')
    

    Here’s the output that I get:

    <VehicleList>
      <Vehicle>
        <Plate>34-23-nd</Plate>
        <ID>1</ID>
        <Section>carros</Section>
        <Category>cabrio</Category>
        <Brand>Mercedes-Benz</Brand>
        <Model>A140</Model>
        <Version>1.4 twin-turbo</Version>
        <Fuel>Gasolina</Fuel>
        <Price>18.000</Price>
        <B2BPrice>0</B2BPrice>
        <Year>2015</Year>
        <Month>03</Month>
        <TollClass>2</TollClass>
        <Origin>Alemanha</Origin>
        <Color>Vermelho</Color>
        <SeatColour>Cinza</SeatColour>
        <Seats>5</Seats>
        <Kms>13000</Kms>
        <Doors>5</Doors>
        <HP>310</HP>
        <Owners>0</Owners>
        <CC>1.389</CC>
        <Obs>Tinta raspada no para-choques dianteiro</Obs>
        <WarrantyMonth>0</WarrantyMonth>
        <AdStand>
          <Name>Stand Veloso</Name>
          <Street>Rua dos Biscainhos</Street>
          <Locality>2450-341</Locality>
          <Email>standbisca@gmail.com</Email>
        </AdStand>
        <ExportSites>
          <Site>Coisas</Site>
        </ExportSites>
        <ExportSites>
          <Site>Custojusto</Site>
        </ExportSites>
        <ExportSites>
          <Site>StandVirtual</Site>
        </ExportSites>
      </Vehicle>
    </VehicleList>
    

    The “mistake” is in the last lines

            <ExportSites>
                  <Site>Coisas</Site>
                </ExportSites>
                <ExportSites>
                  <Site>Custojusto</Site>
                </ExportSites>
                <ExportSites>
                  <Site>StandVirtual</Site>
                </ExportSites>
    

    The output apears “separated” while I need absolutelly opposite, I need it to appear like this [hardcoded]:

                <ExportSites>
                  <Site>Coisas</Site>
                  <Site>Custojusto</Site>
                  <Site>StandVirtual</Site>
                </ExportSites>
    

    What do I need to change in my query so that I get this. Also, can I later export this query with PHP to an XML file?

    Thanks for your time.

    2 Solutions collect form web for “Can't manage to export multiple rows from database to XML”

    Try this:

    DECLARE @mockup TABLE([Site] VARCHAR(100))
    INSERT INTO @mockup VALUES ('Site 1'),('Site 2');
    
    SELECT [Site] FROM @mockup FOR XML PATH('ExportSites'),TYPE;
    SELECT [Site] FROM @mockup FOR XML PATH(''),ROOT('ExportSites'),TYPE;
    

    The results

    <ExportSites>
      <Site>Site 1</Site>
    </ExportSites>
    <ExportSites>
      <Site>Site 2</Site>
    </ExportSites>
    

    and

    <ExportSites>
      <Site>Site 1</Site>
      <Site>Site 2</Site>
    </ExportSites>
    

    You see the difference?

    PATH() will specify the row’s name, while ROOT will define an additional enclosing element. Since your row’s name is empty, the columns name is alone

    Just for curiosity: You might try to add one more column to the mockup-table above and compare the difference…

    So change your sub-query to:

    (SELECT DISTINCT Site 
     from tbl_ExportSites 
     where tbl_ExportSites.ID = tbl_veiculos.ID AND tbl_veiculos.ID = 1 
     FOR XML PATH(''),ROOT('ExportSites'), type )
    

    I think you need to provide the root as below:

    SELECT Plate, tbl_veiculos.ID, Section, Category, Brand, Model, Version, Fuel, Price, B2BPrice, Year, Month, TollClass, Origin, Color, SeatColour, Seats, Kms, Doors, HP, Owners, CC, Obs, TaxDeductible, WarrantyMonth,
    (SELECT Name, Street, Locality, Email from tbl_AdStand where tbl_AdStand.ID = tbl_veiculos.ID AND tbl_veiculos.ID = 1 FOR XML PATH('AdStand'), type ), 
    (SELECT DISTINCT Site from tbl_ExportSites where tbl_ExportSites.ID = tbl_veiculos.ID AND tbl_veiculos.ID = 1 FOR XML PATH('Site'), type, root('ExportSites') )
    FROM tbl_veiculos, tbl_veiculo_spec, tbl_AdStand
    WHERE tbl_veiculos.ID = tbl_veiculo_spec.ID AND tbl_AdStand.ID = tbl_veiculos.ID AND tbl_veiculos.ID = 1
    FOR XML PATH ('Vehicle'), TYPE, ROOT('VehicleList')
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.