Solved

FOR XML -- TSQL

Posted on 2013-05-12
10
489 Views
Last Modified: 2013-05-20
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
0
Comment
Question by:ravichand-sql
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39160103
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
 

Author Comment

by:ravichand-sql
ID: 39160112
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39160116
Ah, yes I see it now, sorry
0
 

Author Comment

by:ravichand-sql
ID: 39162966
Hello Paul,

Could you please help me out with the query!

Thanks in advance!!!
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39163161
>> 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:ravichand-sql
ID: 39163178
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39165140
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
 

Author Comment

by:ravichand-sql
ID: 39165304
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39165797
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
 

Author Closing Comment

by:ravichand-sql
ID: 39181549
Thanks a lot Kevin!!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now