?
Solved

FOR XML -- TSQL

Posted on 2013-05-12
10
Medium Priority
?
497 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 49

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 49

Expert Comment

by:PortletPaul
ID: 39160116
Ah, yes I see it now, sorry
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

Author Comment

by:ravichand-sql
ID: 39162966
Hello Paul,

Could you please help me out with the query!

Thanks in advance!!!
0
 
LVL 41

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
 

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 60

Accepted Solution

by:
Kevin Cross earned 1500 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 60

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Create a Windows 10 custom Image with custom task bar and custom start menu using XML for deployment.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

800 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