March 10, 2011 at 3:45 pm
I'm trying to create a xml tree by writing a sql query. Here is what I have:
DECLARE @CountOfPortfolioObjects int, @CountOfPortfolioGroupObjects int
SELECT @CountOfPortfolioGroupObjects = COUNT(DISTINCT ed.entity_id)
, @CountOfPortfolioObjects = COUNT(entity_detail_id)
FROM entity_detail ed;
WITH Portfolios(CountOfPortfolioGroupObjects, CountOfPortfolioObjects, EntityID)
AS
(
SELECT @CountOfPortfolioGroupObjects
,@CountOfPortfolioObjects
,Entity_Id
FROM entity
)
SELECT Portfolios.CountOfPortfolioGroupObjects
,Portfolios.CountOfPortfolioObjects
,Portfolio.EffectiveDate
,Portfolio.PortfolioCode
,Portfolio.PortfolioGroup
,Portfolio.IntlDomesticInd
,PortfolioGroup.CountPortfolioDetails
FROM Portfolios
INNER JOIN(SELECT CAST(upd_date AS date) AS EffectiveDate
,SUBSTRING(entity_id, 2, 3) AS PortfolioCode
,legal_name AS PortfolioGroup
,'' AS IntlDomesticInd
,entity_id
FROM Entity) Portfolio
ON Portfolios.Entity_ID = Portfolio.entity_id
INNER JOIN (SELECT COUNT(ED.entity_detail_id) AS CountPortfolioDetails
,E.entity_id
,E.legal_name
FROM Entity E
INNER JOIN entity_detail ED
ON E.entity_id = ED.entity_detail_id
GROUP BY E.entity_id
,E.legal_name) PortfolioGroup
ON Portfolios.entity_id = PortfolioGroup.entity_id
FOR XML AUTO, TYPE
Please note that I haven't written the sql query for PortfolioLink yet.
The basic structure of the xml is:
<Portfolios attributes>
<Portfolio attributes />
<PortfolioGroup attributes>
< PortfolioLink attributes />
</PortfolioGroup>
</Portfolios>
However, with the sql query that I've written the xml tree structure is becoming:
<Portfolios attributes>
<Portfolio attributes>
<PortfolioGroup attributes>
</PortfolioGroup>
</Portfolio>
</Portfolios>
I've also tried this sql query
SELECT @CountOfPortfolioGroupObjects AS CountOfPortfolioGroupObjects
,@CountOfPortfolioObjects AS CountOfPortfolioObjects
,(SELECT CAST(upd_date AS date) EffectiveDate
,RTRIM(entity_id) AS PortfolioCode
,RTRIM(legal_name) AS PortfolioGroup
,'' AS IntlDomInd
FROM Entity AS PortfolioLink
FOR XML AUTO, TYPE, ROOT('Portfolios'))
, PortfolioGroup.CountPortfolioDetails
FROM Portfolios Portfolios
INNER JOIN (SELECT COUNT(ED.entity_detail_id) AS CountPortfolioDetails
,E.entity_id
,E.legal_name
FROM Entity E
INNER JOIN entity_detail ED
ON E.entity_id = ED.entity_detail_id
GROUP BY E.entity_id
,E.legal_name) PortfolioGroup
ON PortfolioGroup.entity_id = PortfolioGroup.entity_id
FOR XML AUTO, TYPE
But I get this error:
An XML operation resulted an XML data type exceeding 2GB in size. Operation aborted.
Any help would be appreciated.
March 10, 2011 at 4:40 pm
The error message tells you exaclty what the problem is: you're trying to create an XML structure that exceeds 2GB.
Are you sure you want to create such a large XML file?
March 10, 2011 at 7:24 pm
Thanks for the reply.
The data isn't that large so not sure the reason for the error. The first query doesn't give me that error.
March 10, 2011 at 7:50 pm
Heh... ya just gotta love XML "Tag bloat". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2011 at 10:20 am
After a second look at your query the reason might be the accidential cross join due to
ON PortfolioGroup.entity_id = PortfolioGroup.entity_id
that might better be
ON Portfolios.entity_id = PortfolioGroup.entity_id
March 11, 2011 at 2:36 pm
Good catch 🙂
I've advanced further from my last post, but it's a pain to produce an xml nested tree out of sql. What's considered the best practice to produce an xml tree from tables? An ETL tool?
March 11, 2011 at 2:48 pm
I'm not sure about other tools, but I've found that for creating complex XML structures using T-SQL, you're better off using FOR XML EXPLICIT instead of any of the other FOR XML options. FOR XML EXPLICIT does take a lot of upfront work, but it's worth it in terms of being able to specify your document layout precisely and troubleshoot any problems.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 11, 2011 at 2:48 pm
If you could provide some ready to use test data (as described in the first link in my signature) I could give it a try. (I'd like to test my solution before posting...)
March 11, 2011 at 3:02 pm
drew.allen (3/11/2011)
I'm not sure about other tools, but I've found that for creating complex XML structures using T-SQL, you're better off using FOR XML EXPLICIT instead of any of the other FOR XML options. FOR XML EXPLICIT does take a lot of upfront work, but it's worth it in terms of being able to specify your document layout precisely and troubleshoot any problems.Drew
I'm thinking of an alternative to FOR XML EXPLICIT but I'd like to see if it applies to the given scenario...
March 15, 2011 at 12:55 pm
Unfortunately, I can't post any data. With the help of a co-worker I got the proper XML tree structure.
SELECT Portfolios.CountOfPortfolioGroupObjects
,Portfolios.CountOfPortfolioObjects
--Portfolio
,(SELECT EffectiveDate
,RTRIM(PortfolioCode) PortfolioCode
,PortfolioGroup
,IntlDomInd
FROM @Portfolio Portfolio
FOR XML AUTO, TYPE)
--Portfolio Group
,(select PortfolioGroup.CountPortfolioDetails
,PortfolioGroup.GroupCode
,PortfolioGroup.GroupCode AS GroupName
--Portfolio Link
,(SELECT RTRIM(entity_id) as GroupCode
,RTRIM(entity_detail_id) PortfolioCode
,'' AS IntlDomInd
FROM Entity_detail AS PortfolioLink
Where PortfolioLink.entity_id = PortfolioGroup.GroupCode
FOR XML AUTO, TYPE)
FROM Portfolios
INNER JOIN (SELECT COUNT(entity_detail_id) AS CountPortfolioDetails
,RTRIM(entity_id) AS GroupCode
FROM entity_detail
GROUP BY entity_id) PortfolioGroup
ON PortfolioGroup.GroupCode = Portfolios.EntityID
FOR XML AUTO, TYPE)
FROM Portfolios AS Portfolios
FOR XML AUTO, TYPE
I need to add a header to the outputted xml query: <?xml version="1.0" encoding="utf-8"?>. So I'm following the tutorial from http://blogs.msdn.com/b/sqlprogrammability/archive/2006/04/13/576095.aspx. I don't understand why the below has to be done even though it works.
INSERT INTO @myXML(test, myxml)
SELECT 1, (
SELECT Portfolios.CountOfPortfolioGroupObjects
,Portfolios.CountOfPortfolioObjects
--Portfolio
,(SELECT EffectiveDate
,RTRIM(PortfolioCode) PortfolioCode
,PortfolioGroup
,IntlDomInd
FROM @Portfolio Portfolio
FOR XML AUTO, TYPE)
--Portfolio Group
,(select PortfolioGroup.CountPortfolioDetails
,PortfolioGroup.GroupCode
,PortfolioGroup.GroupCode AS GroupName
--Portfolio Link
,(SELECT RTRIM(entity_id) as GroupCode
,RTRIM(entity_detail_id) PortfolioCode
,'' AS IntlDomInd
FROM Entity_detail AS PortfolioLink
Where PortfolioLink.entity_id = PortfolioGroup.GroupCode
FOR XML AUTO, TYPE)
FROM Portfolios
INNER JOIN (SELECT COUNT(entity_detail_id) AS CountPortfolioDetails
,RTRIM(entity_id) AS GroupCode
FROM entity_detail
GROUP BY entity_id) PortfolioGroup
ON PortfolioGroup.GroupCode = Portfolios.EntityID
FOR XML AUTO, TYPE)
FROM Portfolios AS Portfolios
FOR XML AUTO, TYPE)
March 15, 2011 at 1:31 pm
You don't have to post any actual data. Either make something up or use AdventureWorks tables that would describe your scenario. Once you have some sample data you should post your expected result based on the sample.
August 14, 2011 at 8:52 pm
IN my case i have to create this much xml data . My i am getting this error "XML operation resulted an XML data type exceeding 2GB in size. Operation aborted."
Any workaround.
-Saurabh
August 15, 2011 at 1:12 am
sauraviit-689328 (8/14/2011)
IN my case i have to create this much xml data . My i am getting this error "XML operation resulted an XML data type exceeding 2GB in size. Operation aborted."Any workaround.
-Saurabh
The only option is to create the XML in batches and build the final xml as NVARCHAR(MAX). Basically you'll need to create the node level below the root level (assuming this is a multi node level) and add the root level at the beginning and the end of the final file. Of course, this assumes you'll create a file and aren't trying to send the data to any front end application.
August 15, 2011 at 1:19 pm
If you're really creating XML data files larger than 2GB (esepcially if you start getting into multiples of 2GB), then your long-term solution is - don't use T-SQL to do it.
Use SSIS if need be, or C#, Java, or some other procedural language with a "stream writer" ability.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 15, 2011 at 2:28 pm
How can i do that in ssis. Can you give me an example.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply