FOR XML -- TSQL

Hello Experts,

I have a table with 4 columns: Owner, CustomerName, Eff.StartDate, Eff.EndDate. I want to generate XML from this table and the expected XML sample is attached (expectedsample_5122013.xml) to this question.

The table create and insert script is below:

CREATE TABLE [dbo].[tblMembership](
	[Owner] [varchar](20) NULL,
	[CustomerName] [varchar](20) NULL,
	[EffectiveStartDate] [date] NULL,
	[EffectiveEndDate] [date] NULL
) 

GO

select * from dbo.tblMembership

INSERT INTO dbo.tblMembership
VALUES ('15606','038WU9AF2','01/01/2000','12/31/4700')

INSERT INTO dbo.tblMembership
VALUES ('15606','23R74V200','01/01/2000','12/31/4700')

INSERT INTO dbo.tblMembership
VALUES ('15606','440480K00','01/01/2000','12/31/4700')

INSERT INTO dbo.tblMembership
VALUES ('45601','169FOFYF1','01/01/2000','12/31/4700')

INSERT INTO dbo.tblMembership
VALUES ('45601','AA0891057','01/01/2000','12/31/4700')

select * from dbo.tblMembership

Open in new window


I took help from one of the experts from EE and came up with the below TSQL query.

SELECT 'Membership' AS "@EntryType"
     , '.EjbQlParameters' AS "Property/@Name"
     , 'List' AS "Property/@Type"
     , [Owner] AS "Property/@Value"
     , (SELECT COLUMN_NAME AS "@FieldName"
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'tblMembership'
        AND COLUMN_NAME IN ('Owner', 'CustomerName', 'EffectiveStartDate', 'EffectiveEndDate')
        ORDER BY ORDINAL_POSITION
        FOR XML PATH('FieldConf'), TYPE)
     , (SELECT [Owner], CustomerName, EffectiveStartDate, EffectiveEndDate
        FROM dbo.tblMembership Membership
        WHERE Membership.[Owner] = p.[Owner]
        FOR XML AUTO, TYPE)
FROM dbo.tblMembership p
GROUP BY [Owner]
FOR XML PATH('EntryConf'), ROOT('Entries')
;

Open in new window


Please run the scripts and see the difference between the expected xml format and the one that is generated from the above query. The xml generated from the above query is what I thought would satisfy my user, but he is asking me to produce excatly the same as in the attached (expectedsample.xml) xml file.

Please help me in modifying the query.

Thanks in advance!!!
expectedsample-5122013.xml
ravichand-sqlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
seems that only teh date format is different (I think), so:
SELECT 'Membership' AS "@EntryType"
     , '.EjbQlParameters' AS "Property/@Name"
     , 'List' AS "Property/@Type"
     , [Owner] AS "Property/@Value"
     , (SELECT COLUMN_NAME AS "@FieldName"
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'tblMembership'
        AND COLUMN_NAME IN ('Owner', 'CustomerName', 'EffectiveStartDate', 'EffectiveEndDate')
        ORDER BY ORDINAL_POSITION
        FOR XML PATH('FieldConf'), TYPE)
     , (SELECT [Owner], CustomerName, convert(varchar, EffectiveStartDate ,103) as EffectiveStartDate , convert(varchar, EffectiveEndDate ,103) as EffectiveEndDate
        FROM dbo.tblMembership Membership 
        WHERE Membership.[Owner] = p.[Owner]
        FOR XML AUTO, TYPE)
FROM dbo.tblMembership p
GROUP BY [Owner]
FOR XML PATH('EntryConf'), ROOT('Entries')

Open in new window

0
ravichand-sqlAuthor Commented:
Hello PortletPaul,

I want the membership out of the entryconf. Please review my sample and help me in modifying the query.

Thanks a lot!!
0
PortletPaulfreelancerCommented:
Ah, yes I see it now, sorry
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

ravichand-sqlAuthor Commented:
Hello Paul,

Could you please help me out with the query!

Thanks in advance!!!
0
SharathData EngineerCommented:
>> I want the membership out of the entryconf.
I didn't understand. Can you point out the difference between your expected and Paul's result?
0
ravichand-sqlAuthor Commented:
Hello Sharath,

If you see the expected xml file, membership is inside the EntryConf and I want it to be outside the EntryConf.

Right now, it gives me this result:
<Entries>
  <EntryConf>
    <Membership>
  </EntryConf>
</Entries>

I want to have:

 <Entries>
  <EntryConf>
   </EntryConf>
   <Membership>
</Entries>

Does this make sense? Please review the attached sample to this question.

Thanks a lot!!
0
Kevin CrossChief Technology OfficerCommented:
Hi.

There likely are better ways to do this (see FOR XML EXPLICIT); however, here is a quick alteration of what you have.

SELECT (SELECT 'Membership' AS "@EntryType"
             , '.EjbQlParameters' AS "Property/@Name"
             , 'List' AS "Property/@Type"
             , [Owner] AS "Property/@Value"
             , (SELECT COLUMN_NAME AS "@FieldName"
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = 'tblMembership'
                AND COLUMN_NAME IN ('Owner', 'CustomerName', 'EffectiveStartDate', 'EffectiveEndDate')
                ORDER BY ORDINAL_POSITION
                FOR XML PATH('FieldConf'), TYPE)
        FOR XML PATH('EntryConf'), TYPE)
     , (SELECT [Owner], CustomerName, EffectiveStartDate, EffectiveEndDate
        FROM dbo.tblMembership Membership
        WHERE Membership.[Owner] = p.[Owner]
        FOR XML AUTO, TYPE)
FROM dbo.tblMembership p
GROUP BY [Owner]
FOR XML PATH(''), ROOT('Entries')
;

Open in new window


I hope this helps for now. If you need the date in different format, see the CONVERT() example from the Expert above. You would just use the appropriate format code.
MSDN: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Best regards,

Kevin

P.S. References for FOR XML EXPLICIT:
http://msdn.microsoft.com/en-us/library/ms189068.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ravichand-sqlAuthor Commented:
Thanks a lot Kevin,

I was trying to do this: I was trying this on my original data from a view which has 20,000 rows.
 
DECLARE @XML XML

SELECT @XML = (
 SELECT (SELECT 'Membership' AS "@EntryType"
             , '.EjbQlParameters' AS "Property/@Name"
             , 'List' AS "Property/@Type"
             , [Owner] AS "Property/@Value"
             , (SELECT COLUMN_NAME AS "@FieldName"
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = 'vwModelNMembership'
                AND COLUMN_NAME IN ('Owner', 'CustomerName', 'EffectiveStartDate', 'EffectiveEndDate')
                ORDER BY ORDINAL_POSITION
                FOR XML PATH('FieldConf'), TYPE)
        FOR XML PATH('EntryConf'), TYPE)
     , (SELECT [Owner], CustomerName, EffectiveStartDate, EffectiveEndDate
        FROM dbo.vwModelNMembership Membership
        WHERE Membership.[Owner] = p.[Owner]
        FOR XML AUTO, TYPE)
FROM dbo.vwModelNMembership p
GROUP BY [Owner]
FOR XML PATH(''), ROOT('Entries')
)

SELECT @XML

Open in new window


But, when I clcik on the ouput in SSMS. I am getting the following error:

Unable to show XML. The following error happened:
There is an unclosed literal string. Line 1, position 2097154.

One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.


I changed the Maximum Characters Retrieved for XML data to unlimited. but there is no difference in the output.

Please help!
0
Kevin CrossChief Technology OfficerCommented:
The limitation is with viewing the data in SSMS. Therefore, you can take a sample (e.g., TOP 100) to see what the format looks like in SSMS before sending to your application. If you change the setting for results to grid you described above, restart SSMS (the setting does not take effect until you do -- at least it did not for me), then re-run the query. Instead of clicking on the results, right-click and select "Save Results As..." and save the XML result as Entries.xml for example. The XML document should be complete. I ran mine for 30,000+ rows and it works just fine.
0
ravichand-sqlAuthor Commented:
Thanks a lot Kevin!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.