June 24, 2010 at 8:26 am
Given these three tables
CREATE TABLE Table_1(
[id] [int] NOT NULL,
[f1] [varchar](50) NULL,
[f2] [varchar](50) NULL
CREATE TABLE Table_2(
[id] [int] NOT NULL,
[f21] [varchar](50) NULL,
[f22] [varchar](50) NULL)
CREATE TABLE Table_3(
[id] [int] NOT NULL,
[f31] [varchar](50) NULL,
[f32] [varchar](50) NULL)
Where both table_2 and table_3 can have one or many records related to table_1.
I want to get results back that look something like ...
<Table_1>
<id>1</id>
<f1>f1 data</f1>
<f2>f2 data</f2>
<Table_2>
<id>1</id>
<f21>f21 data row1</f21>
<f22>f22 data row1</f22>
</Table_2>
<Table_2>
<id>1</id>
<f21>f21 data row2</f21>
<f22>f22 data row2</f22>
</Table_2>
<Table_3>
<id>1</id>
<f31>f31 data row1</f31>
<f32>f32 data row1</f32>
</Table_3>
<Table_3>
<id>1</id>
<f31>f31 data row1</f31>
<f32>f32 data row1</f32>
</Table_3>
</Table_1>
But I get this back instead ...
<Table_1>
<id>1</id>
<f1>f1 data</f1>
<f2>f2 data</f2>
<Table_2>
<id>1</id>
<f21>f21 data row1</f21>
<f22>f22 data row1</f22>
<Table_3>
<id>1</id>
<f31>f31 data row1</f31>
<f32>f32 data row1</f32>
</Table_3>
</Table_2>
<Table_2>
<id>1</id>
<f21>f21 data row2</f21>
<f22>f22 data row2</f22>
<Table_3>
<id>1</id>
<f31>f31 data row1</f31>
<f32>f32 data row1</f32>
</Table_3>
</Table_2>
<Table_2>
<id>1</id>
<f21>f21 data row1</f21>
<f22>f22 data row1</f22>
<Table_3>
<id>1</id>
<f31>f31 data row2</f31>
<f32>f32 data row2</f32>
</Table_3>
</Table_2>
<Table_2>
<id>1</id>
<f21>f21 data row2</f21>
<f22>f22 data row2</f22>
<Table_3>
<id>1</id>
<f31>f31 data row2</f31>
<f32>f32 data row2</f32>
</Table_3>
</Table_2>
</Table_1>
My current query looks like this ...
SELECT
Table_1.id, Table_1.f1, Table_1.f2,
Table_2.id AS 'id', Table_2.f21, Table_2.f22,
Table_3.id AS 'id', Table_3.f31, Table_3.f32
FROM Table_1
INNER JOIN Table_2 ON Table_1.id = Table_2.id
INNER JOIN Table_3 ON Table_1.id = Table_3.id
FOR XML AUTO, ELEMENTS
Does anyone have any ideas to get me the correct format? I do not want table_3 within table_2. They should be at the same level.
June 24, 2010 at 11:02 am
I don't think you can do it with FOR XML AUTO, you need to use EXPLICIT option.
Try:
-- setup test data
/*
insert into Table_1 select 1, 'f1 data', 'f2 data'
insert into Table_2 select 1, 'f21 data row1', 'f22 data row1'
insert into Table_2 select 1, 'f21 data row2', 'f22 data row2'
insert into Table_3 select 1, 'f31 data row1', 'f32 data row1'
insert into Table_3 select 1, 'f31 data row2', 'f32 data row2'
*/
SELECT 1 as Tag
,Null as Parent
,id as [Table_1!1!id!ELEMENT]
,f1 as [Table_1!1!f1!ELEMENT]
,f2 as [Table_1!1!f2!ELEMENT]
,null as [Table_2!2!id!ELEMENT]
,null as [Table_2!2!f21!ELEMENT]
,null as [Table_2!2!f22!ELEMENT]
,null as [Table_3!3!id!ELEMENT]
,null as [Table_3!3!f31!ELEMENT]
,null as [Table_3!3!f32!ELEMENT]
FROM Table_1
UNION ALL
SELECT 2 as Tag
,1 as Parent
,Table_1.id
,Table_1.f1
,Table_1.f2
,Table_2.id
,Table_2.f21
,Table_2.f22
,null
,null
,null
FROM Table_1
INNER JOIN Table_2 ON Table_1.id = Table_2.id
UNION ALL
SELECT 3 as Tag
,1 as Parent
,Table_1.id
,Table_1.f1
,Table_1.f2
,null
,null
,null
,Table_3.id
,Table_3.f31
,Table_3.f32
FROM Table_1
INNER JOIN Table_3 ON Table_1.id = Table_3.id
FOR XML EXPLICIT
June 28, 2010 at 11:27 am
Thank you Eugene for your post.
This is very close. The code you suplied returns the following. If you take a look, you will se that the first "Table_1" is an extra root element.
I see why its getting generated, but as of yet I have not been able to eliminate it. Do you have an idea of how I could not generat the first "Table_1" root elelement?
<Table_1>
<id>1</id>
<f1>f1 data</f1>
<f2>f2 data</f2>
</Table_1>
<Table_1>
<id>2</id>
<f1>f1 data row2</f1>
<f2>f2 data row2</f2>
<Table_2>
<id>1</id>
<f21>f21 data row1</f21>
<f22>f22 data row1</f22>
</Table_2>
<Table_2>
<id>1</id>
<f21>f21 data row2</f21>
<f22>f22 data row2</f22>
</Table_2>
<Table_3>
<id>1</id>
<f31>f31 data row1</f31>
<f32>f32 data row1</f32>
</Table_3>
<Table_3>
<id>1</id>
<f31>f31 data row2</f31>
<f32>f32 data row2</f32>
</Table_3>
</Table_1>
June 28, 2010 at 3:34 pm
I think I got it. There was another problem with the previous code. The relationships were not correct in the XML code. I had ID's of 2 mixed in with ID's of 1.
I went about it a little different.
This is what my code is now ...
SELECT
id
,f1
,f2
,(Select
id as 't2.id'
,f21
,f22
From Table_2 t2
where t1.id = t2.id
FOR XML PATH('Table2'), type)
,(Select
id as 't3.id'
,f31
,f32
From Table_3 t3 where t1.id = t3.id
FOR XML PATH('Table3'), type)
From Table_1 t1
order by t1.id
FOR XML PATH('Table1'), ROOT('Tables')
The results of this code look like this ...
<Tables>
<Table1>
<id>1</id>
<f1>f1 data</f1>
<f2>f2 data</f2>
<Table2>
<t2.id>1</t2.id>
<f21>f21 data row1 id1</f21>
<f22>f22 data row1 id1</f22>
</Table2>
<Table2>
<t2.id>1</t2.id>
<f21>f21 data row2 id1</f21>
<f22>f22 data row2 id1</f22>
</Table2>
<Table3>
<t3.id>1</t3.id>
<f31>f31 data row1 id1</f31>
<f32>f32 data row1 id1</f32>
</Table3>
<Table3>
<t3.id>1</t3.id>
<f31>f31 data row2 id1</f31>
<f32>f32 data row2 id1</f32>
</Table3>
</Table1>
<Table1>
<id>2</id>
<f1>f1 data row2</f1>
<f2>f2 data row2</f2>
<Table2>
<t2.id>2</t2.id>
<f21>f21 Data row 3 id2</f21>
<f22>f22 data rowe id2</f22>
</Table2>
<Table3>
<t3.id>2</t3.id>
<f31>f31 data row3 id2</f31>
<f32>f32 data row3 id2 </f32>
</Table3>
</Table1>
</Tables>
June 29, 2010 at 7:31 am
Your query indeed renders the required XML. However, using subselects will cause to many scans and reads. I have modified my one to render the required XML for you. You can use "SET STATISTICS IO ON" to see number of scans and reads performed by both quieries. You will see how adding more data will cause more and more scans & reads in the query where subselects are used, contrary to the posted one, where it will be pretty much constant: 3 scans and reads of Table 1, and 1 scan/read of Table_2 and Table_3. You can format select into one line to safe space on your screen if you have a small one :-D.
Please note, I am not aware of your index structure...
--Setup
/*
--drop table Table_1
--drop table Table_2
--drop table Table_3
go
CREATE TABLE Table_1(
[id] [int] NOT NULL,
[f1] [varchar](50) NULL,
[f2] [varchar](50) NULL )
CREATE TABLE Table_2(
[id] [int] NOT NULL,
[f21] [varchar](50) NULL,
[f22] [varchar](50) NULL)
CREATE TABLE Table_3(
[id] [int] NOT NULL,
[f31] [varchar](50) NULL,
[f32] [varchar](50) NULL)
go
insert into Table_1 select 1, 'f1 data', 'f2 data'
insert into Table_2 select 1, 'f21 data row1', 'f22 data row1'
insert into Table_2 select 1, 'f21 data row2', 'f22 data row2'
insert into Table_3 select 1, 'f31 data row1', 'f32 data row1'
insert into Table_3 select 1, 'f31 data row2', 'f32 data row2'
insert into Table_1 select 2, '2f1 data', '2f2 data'
insert into Table_2 select 2, '2f21 data row1', '2f22 data row1'
insert into Table_2 select 2, '2f21 data row2', '2f22 data row2'
insert into Table_3 select 2, '2f31 data row1', '2f32 data row1'
insert into Table_3 select 2, '2f31 data row2', '2f32 data row2'
insert into Table_1 select 3, '3f1 data', '3f2 data'
insert into Table_2 select 3, '3f21 data row1', '3f22 data row1'
insert into Table_2 select 3, '3f21 data row2', '3f22 data row2'
insert into Table_3 select 3, '3f31 data row1', '3f32 data row1'
insert into Table_3 select 3, '3f31 data row2', '3f32 data row2'
*/
-- SET STATISTICS IO ON
SELECT 1 as Tag
,Null as Parent
,'' AS [Tables!1]
,null as [Table_1!2!id!ELEMENT]
,null as [Table_1!2!f1!ELEMENT]
,null as [Table_1!2!f2!ELEMENT]
,null as [Table_2!3!id!ELEMENT]
,null as [Table_2!3!f21!ELEMENT]
,null as [Table_2!3!f22!ELEMENT]
,null as [Table_3!4!id!ELEMENT]
,null as [Table_3!4!f31!ELEMENT]
,null as [Table_3!4!f32!ELEMENT]
UNION ALL
SELECT 2 as Tag
,1 as Parent
,null
,Table_1.id
,Table_1.f1
,Table_1.f2
,null
,null
,null
,null
,null
,null
FROM Table_1
UNION ALL
SELECT 3 as Tag
,2 as Parent
,null
,Table_1.id
,Table_1.f1
,Table_1.f2
,Table_2.id
,Table_2.f21
,Table_2.f22
,null
,null
,null
FROM Table_1
INNER JOIN Table_2 ON Table_2.id = Table_1.id
UNION ALL
SELECT 4 as Tag
,2 as Parent
,null
,Table_1.id
,Table_1.f1
,Table_1.f2
,null
,null
,null
,Table_3.id
,Table_3.f31
,Table_3.f32
FROM Table_1
INNER JOIN Table_3 ON Table_3.id = Table_1.id
ORDER BY [Table_1!2!id!ELEMENT], Tag
FOR XML EXPLICIT
June 29, 2010 at 10:21 am
Eugene,
First Thanks for your time. I was able to take your sample and use it for my actual set of tables and it worked great.
I do have a question however. If I were to compare the two ways of doing this without a where clause your method does indeed produce less io. In fact, using the Sub Queries I was not able to run thru my whole file. But, if I add a where clause ("where Table_1.id = 1”) then the sub query model is has a little less io.
I will always be selecting by the id, do you think the Sub Query or using the Unions would still be a better model when selecting rows for a specific id?
I made ID the primary key for table 1, ID= non-unique index’s on the other two. Which actual increased the io.
June 29, 2010 at 10:28 am
In this case, you can use FOR XML PATH (it takes much less space than FOR EXPLICIT "query from hell"), as you will only read Table_2 and Table_3 once for the required id.
Actually, you can implement both queries. Use your's one when you need to get data for one ID and use FOR EXPLICIT when extracting all of the data (or range) in one go (it may be required for search functionality etc.).
June 29, 2010 at 10:32 am
Great, Thanks. We were thinking that here also, but wanted to make sure we understood what the numbers were telling us.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply