help tweak my FOR XML EXPLICIT example?

Question

help tweak my FOR XML EXPLICIT example?

Lowell

SSC Guru

Points: 323518
More actions
June 6, 2012 at 7:00 am

#74316

I'm trying to build a good-for-me to understand example of FOR XML EXPLICIT.
i'm using a books online example as a model.
http://msdn.microsoft.com/en-us/library/bb522438.aspx
i'm just selecting from sys.tables/sys.columns to get a simple parent-child example.
my problem is that compared to the MS sample results, my "parent" object node is repeated once for each column, and only one of the results has the child columns under it correctly.
can anyone see my obvious mistake?
--modeled after
--http://msdn.microsoft.com/en-us/library/bb522438.aspx
SELECT
1 AS TAG,
NULL AS PARENT,
SCHEMA_NAME(objz.schema_id) AS [OBJECT!1!SchemaName],
objz.name AS [OBJECT!1!TableName],
objz.object_id AS [OBJECT!1!ObjectID],
NULL AS [Columns!2!ColumnName],
NULL AS [Columns!2!DataType]
FROM sys.tables objz
INNER JOIN sys.columns colz
ON objz.object_id = colz.object_id
UNION ALL
SELECT
2 AS TAG,
1 AS PARENT,
SCHEMA_NAME(objz.schema_id),
objz.name ,
objz.object_id,
colz.name AS [ColumnName],
TYPE_NAME(system_type_id) AS [DataType]
FROM sys.tables objz
INNER JOIN sys.columns colz
ON objz.object_id = colz.object_id
ORDER BY [OBJECT!1!SchemaName],[OBJECT!1!ObjectID]
FOR XML EXPLICIT;
example results
/*
<Object SchemaName="dbo" TableName="PPEXP" ObjectID="1487134"/>
<Object SchemaName="dbo" TableName="PPEXP" ObjectID="1487134"/>
<Object SchemaName="dbo" TableName="PPEXP" ObjectID="1487134"/>
<Object SchemaName="dbo" TableName="PPEXP" ObjectID="1487134"/>
<Object SchemaName="dbo" TableName="PPEXP" ObjectID="1487134"/>
<Object SchemaName="dbo" TableName="PPEXP" ObjectID="1487134"/>
<Object SchemaName="dbo" TableName="PPEXP" ObjectID="1487134"/>
<Object SchemaName="dbo" TableName="PPEXP" ObjectID="1487134"/>
<Object SchemaName="dbo" TableName="PPEXP" ObjectID="1487134"/>
<Object SchemaName="dbo" TableName="PPEXP" ObjectID="1487134"/>
<Object SchemaName="dbo" TableName="PPEXP" ObjectID="1487134"/>
<Object SchemaName="dbo" TableName="PPEXP" ObjectID="1487134"/>
<Object SchemaName="dbo" TableName="PPEXP" ObjectID="1487134">
<Columns ColumnName="ACTUAL" DataType="float"/>
<Columns ColumnName="AUDITAMT" DataType="float"/>
<Columns ColumnName="BUDGET" DataType="float"/>
<Columns ColumnName="BUDGETPUPA" DataType="float"/>
<Columns ColumnName="EXPENSE" DataType="int"/>
<Columns ColumnName="EXPTBLKEY" DataType="int"/>
<Columns ColumnName="FORECAST" DataType="float"/>
<Columns ColumnName="FORECASTUNIT" DataType="float"/>
<Columns ColumnName="NOTES" DataType="text"/>
<Columns ColumnName="OPRESERVE" DataType="char"/>
<Columns ColumnName="PERROOMCALC" DataType="float"/>
<Columns ColumnName="REPRESERVE" DataType="char"/>
<Columns ColumnName="VERSIONTBLKEY" DataType="int"/>
</Object>
*/
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Arthur Olcot SSCertifiable Points: 6652 More actions · Answer 1

Here you go, this is a slightly revised version. I just removed the inner join from the first query and added Tag to the order clause. The join was adding all those extra records that you didn't want to see and the TAG in the order by clause ensures you don't get an error from the TAGs not being in the correct order:

--modeled after

--http://msdn.microsoft.com/en-us/library/bb522438.aspx

SELECT

1 AS TAG,

NULL AS PARENT,

SCHEMA_NAME(objz.schema_id) AS [OBJECT!1!SchemaName],

objz.name AS [OBJECT!1!TableName],

objz.object_id AS [OBJECT!1!ObjectID],

NULL AS [Columns!2!ColumnName],

NULL AS [Columns!2!DataType]

FROM sys.tables objz

UNION ALL

SELECT

2 AS TAG,

1 AS PARENT,

SCHEMA_NAME(objz.schema_id),

objz.name ,

objz.object_id,

colz.name AS [ColumnName],

TYPE_NAME(system_type_id) AS [DataType]

FROM sys.tables objz

INNER JOIN sys.columns colz

ON objz.object_id = colz.object_id

ORDER BY [OBJECT!1!SchemaName],[OBJECT!1!ObjectID], TAG

FOR XML EXPLICIT;