CODEWITHSUNDEEP
sqlsql-serverxml
Pavlo Zakharuk
Pavlo Zakharuk

Reputation: 115

How can I output XML with different queries

I am struggling at outputting XML the correct way. I'm trying to generate XML document with SQL Server query, which gives my the following:

CODE USED: 

    SELECT Plate, tbl_veiculos.ID, Brand, Model,  Origin, Color
FROM tbl_veiculos, tbl_veiculo_spec
WHERE tbl_veiculos.ID = tbl_veiculo_spec.ID AND tbl_veiculos.ID = 1
FOR XML PATH ('Vehicle'), TYPE, ROOT('VehicleList')

RESULT: 

    <VehicleList>
      <Vehicle>
        <Plate>34-23-nd</Plate>
        <ID>1</ID>
        <Brand>Mercedes-Benz</Brand>
        <Model>A140</Model>
        <Origin>Germany</Origin>
        <Color>Red</Color>
      </Vehicle>
    </VehicleList>

Which is to a certain way what I need. The problem comes when I try to merge it with other query output. I know this doesn't explain well so I'll show you an harcoded version of what I want. 

            <VehicleList>
              <Vehicle>
                <Plate>34-23-nd</Plate>
                <ID>1</ID>
                <Brand>Mercedes-Benz</Brand>
                <Model>A140</Model>
                <SellerInfo>
                     <Name>Someone Special</Name>
                     <Street>Oxfod Court 1231</Street>
                </SellerInfo>
                <Origin>Germany</Origin>
                <Color>Red</Color>
              </Vehicle>
            </VehicleList>

<SellerInfo> comes from other table.

Upvotes: 0

Views: 34

Answers (1)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You can use subquery as below:

 SELECT Plate, tbl_veiculos.ID, Brand, Model,  
 (select Name, Street from sellerInfo where id = t1.id --use id to join sellerInfo table
  for xml Path(''), type) as SellerInfo,
 Origin, Color,
FROM tbl_veiculos t1 inner join tbl_veiculo_spec ts
on tbl_veiculos.ID = tbl_veiculo_spec.ID AND tbl_veiculos.ID = 1
FOR XML PATH ('Vehicle'), TYPE, ROOT('VehicleList')

Upvotes: 3

Related Questions