February 11, 2016 at 9:25 am
the code above produce this output:
<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>
is it possbile to transform to this:
<add>
<doc>
<field name="Main">1</field>
<field name="Tags">TA</field>
<field name="Docs">DA</field>
<doc>
</add>
<add>
<doc>
<field name="Main">2</field>
<field name="Tags">TB</field>
<field name="Docs">DB</field>
<doc>
</add>
<add>
<doc>
<field name="Main">3</field>
<field name="Tags">TC</field>
<field name="Docs">DC</field>
<doc>
</add>
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')
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
Thanks.
February 11, 2016 at 10:02 am
Yes it is, but in doing so, your field tag now contains integer and character data, so you will have troubles shredding it later.
Select
ca.doc
From Main
Left Join Docs on Main.Id = Docs.Id
Left Join Tags on Main.Id = Tags.Id
CROSS APPLY(
SELECT [@name],
int_data AS [text()],
char_data AS [text()]
FROM (
VALUES
('Main', Main.Id, NULL),
('Tags', NULL, Tags.Tag),
('Docs', NULL, Docs.Doc)
) v([@name], int_data, char_data)
FOR XML PATH('field'), TYPE
) ca(doc)
FOR XML PATH('add'),TYPE
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply