[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

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
0
ravichand-sql
Asked:
ravichand-sql
  • 5
  • 2
  • 2
  • +1
1 Solution
 
PortletPaulCommented:
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
 
PortletPaulCommented:
Ah, yes I see it now, sorry
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now