Solved

FOR XML -- TSQL

Posted on 2013-05-12
10
496 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 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 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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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