December 23, 2015 at 3:23 am
HI, in the following query i've this result:
<Main>
<Tags>
<Docs>
</Docs>
</Tags>
</Main>
i would change to:
<Main>
<Tags></Tags>
<Docs></Docs>
</Main>
Thanks.
Select Main.Id, Tags.Tag , Docs.Doc
From Main
Left Join Docs on Main.Id = Docs.Id
Left Join Tags on Main.Id = Tags.Id
for xml auto
December 23, 2015 at 4:07 am
Quick suggestion
😎
DDL and Sample data
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.Main') IS NOT NULL DROP TABLE dbo.Main;
IF OBJECT_ID(N'dbo.Tags') IS NOT NULL DROP TABLE dbo.Tags;
IF OBJECT_ID(N'dbo.Docs') IS NOT NULL DROP TABLE dbo.Docs;
CREATE TABLE dbo.Main
(
Id INT NOT NULL
,MVAL VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.Tags
(
Id INT NOT NULL
,Tag VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.Docs
(
Id INT NOT NULL
,Doc VARCHAR(10) NOT NULL
);
INSERT INTO dbo.Main(Id,MVAL)
VALUES (1,'MA'),(2,'MB'),(3,'MC');
INSERT INTO dbo.Tags(Id,Tag)
VALUES (1,'TA'),(2,'TB'),(3,'TC');
INSERT INTO dbo.Docs(Id,Doc)
VALUES (1,'DA'),(2,'DB'),(3,'DC');
Query
Select
Main.Id AS '@ID'
,Tags.Tag AS 'Tags'
,Docs.Doc AS 'Docs'
From Main
Left Join Docs on Main.Id = Docs.Id
Left Join Tags on Main.Id = Tags.Id
FOR XML PATH('Main'),TYPE,ROOT('Mains');
Results
<Mains>
<Main ID="1">
<Tags>TA</Tags>
<Docs>DA</Docs>
</Main>
<Main ID="2">
<Tags>TB</Tags>
<Docs>DB</Docs>
</Main>
<Main ID="3">
<Tags>TC</Tags>
<Docs>DC</Docs>
</Main>
</Mains>
December 23, 2015 at 8:12 am
Just as an FYI, the AUTO and RAW options for XML give you very little flexibility. If you need your XML in a specific format, you are usually best off using the PATH option. The EXPLICIT option gives you the most control, but it is also the most difficult to set up correctly.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply