我有一个 3 行的表,1 用作行标识符,第二个用于与其他表建立关系,第三个包含内容。我正在做一个查询,其中给定的结果是我需要的,但有一点错误。
这是我正在使用的查询:
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')
这是我得到的输出:
<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>[email protected]</Email>
</AdStand>
<ExportSites>
<Site>Coisas</Site>
</ExportSites>
<ExportSites>
<Site>Custojusto</Site>
</ExportSites>
<ExportSites>
<Site>StandVirtual</Site>
</ExportSites>
</Vehicle>
</VehicleList>
“错误”在最后几行
<ExportSites>
<Site>Coisas</Site>
</ExportSites>
<ExportSites>
<Site>Custojusto</Site>
</ExportSites>
<ExportSites>
<Site>StandVirtual</Site>
</ExportSites>
输出apears“分离”,而我需要绝对相反,我需要它看起来像这样[硬编码]:
<ExportSites>
<Site>Coisas</Site>
<Site>Custojusto</Site>
<Site>StandVirtual</Site>
</ExportSites>
我需要在我的查询中更改什么才能得到这个。另外,我以后可以使用 PHP 将此查询导出到 XML 文件吗?
谢谢你的时间。
试试这个:
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;
结果
<ExportSites>
<Site>Site 1</Site>
</ExportSites>
<ExportSites>
<Site>Site 2</Site>
</ExportSites>
和
<ExportSites>
<Site>Site 1</Site>
<Site>Site 2</Site>
</ExportSites>
你看出区别了吗?
PATH()
将指定行的名称,同时ROOT
将定义一个附加的封闭元素。由于您的行名称为空,因此列名称是单独的...
只是出于好奇:您可以尝试在上面的模型表中再添加一列并比较差异...
因此,将您的子查询更改为:
(SELECT DISTINCT Site
from tbl_ExportSites
where tbl_ExportSites.ID = tbl_veiculos.ID AND tbl_veiculos.ID = 1
FOR XML PATH(''),ROOT('ExportSites'), type )
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句