Hi Nigel,

I've build MemberExport using a DataReader retrieving the data from the database and then build the csv file. It's fast In my testcase the export of 5000 records took 2142 milliseconds. I suggest you use a Datareader and XMLWriter for your export, then you'll have full control over the output

Cheers,

Richard 

Hi Richard 

Thanks for your reply.

I currently have a script that I run to create an  XML file but for 3,000 records it is taking 30+ seconds to run and at times has even timed out during processing - ouch !

One full node is as follows:

<node id="1281" version="1386a7f6-b7ab-4f6c-98d3-05a49a3ec405" parentID="-1" level="1" 
writerID="0" nodeType="1239" template="0" sortOrder="2" createDate="2009-12-18T10:43:31" 
updateDate="2009-12-18T10:43:31" nodeName="Cadbury" urlName="cadbury" 
writerName="Administrator" nodeTypeAlias="SupplierCompany" path="-1,1281" 
loginName="cadbury" email="[email protected]">
    <data alias="coyLegalEntityName">Cadbury Limited</data>
    <data alias="lastLoginDate" />
    <data alias="foodstuffsGroup">0</data>
    <data alias="coyContactName" />
    <data alias="coyLegalTradingName">Cadbury Chocolates</data>
    <data alias="coyLogo" />
    <data alias="coyPrimaryBusinessCategory" />
    <data alias="coySecondaryBusinessCategory" />
    <data alias="coyAucklandReviewDate" />
    <data alias="coyWellingtonReviewDate" />
    <data alias="coySouthIslandReviewDate" />
    <data alias="coyAucklandVendorID" />
    <data alias="coyWellingtonVendorID" />
    <data alias="coySouthIslandVendorID" />
    <data alias="coyPhone">03 477 3400</data>
    <data alias="coyFax">03 377 3401</data>
    <data alias="coyPhysical1">Princes Street</data>
    <data alias="coyPhysical2" />
    <data alias="coyPhysicalCity">Dunedin</data>
    <data alias="coyPhysicalPostcode">9001</data>
    <data alias="coyPostal1">PO Box 123</data>
    <data alias="coyPostal2" />
    <data alias="coyCity">Dunedin North</data>
    <data alias="coyPostcode">9010</data>
    <data alias="termsOfUse" />
  </node>

I therefore identified that if I could get the SQL query to the desired XML format I can then simply save the data to file.

Doing it this way was proving much much quicker. 

I guess my expectations were that the main umbraco.config re-creates itself very quickly and my data has a similar structure, so was hoping to be able to get similar results.

I am beginning to think I need to reconsider how I store the data in my XMl file and start over.

Thanks

Nigel

I have now found an old Forum post that is along the lines of what I am trying to achieve

http://forum.umbraco.org/yaf_postst1925_Export-members-to-xml--looking-for-a-faster-way.aspx

My query is now returning nicely structured XML data, however there is no "root" node. So far I have not been able to successfully alter the SQL query to include this, nor have I been able to get my user control to successfully write the "root" node as part of the process.

My SQL query is:

SELECT 
  1 as Tag, 
  null as Parent, 
  umbracoNode.[id] AS [node!1!id], 
  umbracoNode.[parentID] AS [node!1!parentID], 
  umbracoNode.[text] AS [node!1!nodeName], 
  '' AS [node!1!urlName], 
  cmsMember.[loginName]AS [node!1!loginName], 
  cmsMember.[email]AS [node!1!email], 
  null AS [data!2!id], 
  null AS [data!2!alias], 
  umbracoNode.[text] AS [data!2!alias],  
  null AS [data!2],  
  CAST(employerData.xml as xml).value('(node/data[@alias="coyLegalEntityName"])[1]', 'nvarchar(200)') AS [node!1!employer] 
FROM umbracoNode 
inner join cmsMember on umbracoNode.[id] = cmsMember.nodeId 
inner join cmsContent on umbracoNode.[id] = cmsContent.nodeId 
inner join cmsContentType on cmsContent.contentType = cmsContentType.nodeId 
inner join cmsContentVersion on umbracoNode.[id] = cmsContentVersion.contentId 
inner join umbracoUser on umbracoNode.nodeUser = umbracoUser.[id]
inner join cmsContentXml AS employeeData ON employeeData.nodeId = umbracoNode.[id] 
inner join cmsContentXml AS employerData ON CAST(employeeData.xml AS xml).value('(node/data[@alias="usrEmployer"])[1]', 'nvarchar(200)') = employerData.nodeId

WHERE cmsContentType.[nodeId] = '1240'

Union All

SELECT 
  2 AS Tag, 
  1 AS Parent, 
  cmsPropertyData.contentNodeId as [node!1!id], 
  null, null, null, null, null, 
  cmsPropertyData.[id], 
  cmsPropertyType.Alias, 
  null, 
  isnull(Convert(Nvarchar(4000),dataInt), 
      isnull(Convert(Nvarchar(4000),dataDate),
          isnull(Convert(Nvarchar(4000),dataNvarchar),
              isnull(Convert(Nvarchar(4000),dataNtext),'')))), 
  null 
FROM 
  umbracoNode
inner join cmsPropertyData on umbracoNode.[id] = cmsPropertyData.contentNodeId
inner join cmsPropertyType on cmsPropertyData.propertytypeid = cmsPropertyType.[id]
inner join cmsContentVersion on umbracoNode.[id] = cmsContentVersion.contentId
inner join cmsContentXml AS employeeData on cmsPropertyData.contentNodeId = employeeData.nodeId
inner join cmsContentXml AS employerData on CAST(employeeData.xml AS xml).value('(node/data[@alias="usrEmployer"])[1]', 'nvarchar(200)') = employerData.nodeId
WHERE 
  cmsPropertyType.contentTypeId = '1240'
ORDER BY [node!1!id], [data!2!id] 
FOR XML Explicit

Can anyone suggest how to add <root id="-1"> to the sql query ?

Thanks

Nigel

Hi Nigel,

On that old forum post, I posted a SQL snippet for a quick way to get the XML for all the members:

SELECT
    m.nodeId AS '@id',
    m.Email AS '@email',
    CAST(x.xml AS XML) AS '*'
FROM
    cmsMember AS m
    INNER JOIN cmsContentXml AS x ON m.nodeId = x.nodeId
FOR XML PATH('member'), ROOT('members'), TYPE

The main downside of this approach is that the XML structure is like this...

<members>
    <member id="1234" email="[email protected]">
        <node ...>
            <data alias="...">...</data>
        </node>
        ...
    </member>
</members>

... with the <node> nested under the <member> element.  But I don't see that as much of a problem.

From your first snippet, it looks like you need/want to filter the members before you get the XML dump? If so, I'd suggest doing that using SQL JOINs with the cmsContent table (or others) - rather than using inline XPath statements in CAST'd XML data, (that's where the timeouts WILL occur).

A quick example:

SELECT
    m.nodeId AS '@id',
    m.Email AS '@email',
    CAST(x.xml AS XML) AS '*'
FROM
    cmsContent AS c
    INNER JOIN cmsMember AS m ON c.nodeId = m.nodeId
    INNER JOIN cmsContentXml AS x ON m.nodeId = x.nodeId
WHERE
    c.contentType IN (1239, 1240)
FOR XML PATH('member'), ROOT('members'), TYPE

Good luck!

Cheers, Lee.

Hi Lee (and others)

Have finally cracked the SQL - the following outputs XML in the structure I want it - all I have to do is wrap a <root> tag around it and save the file.

Within a user control I am running the query and saving the file (approx 3000 nodes) in about 1 second.

SELECT 
        1 as Tag, 
        null as Parent, 
        umbracoNode.[id] AS [node!1!id], 
        umbracoNode.[text] AS [node!1!nodeName], 
        cmsMember.[loginName]AS [node!1!loginName], 
        null AS [data!2!id], 
        null AS [data!2!alias], 
        umbracoNode.[text] AS [data!2!alias],  
        null AS [data!2]
        
FROM [Foodstuffs Suppliers].[dbo].umbracoNode 
inner join [Foodstuffs Suppliers].[dbo].cmsMember on umbracoNode.[id] = cmsMember.nodeId 
inner join [Foodstuffs Suppliers].[dbo].cmsContent on umbracoNode.[id] = cmsContent.nodeId 
inner join [Foodstuffs Suppliers].[dbo].cmsContentType on cmsContent.contentType = cmsContentType.nodeId 
inner join [Foodstuffs Suppliers].[dbo].umbracoUser on umbracoNode.nodeUser = umbracoUser.[id]

WHERE cmsContentType.[nodeId] = '1239' AND cmsMember.[loginName] NOT LIKE 'archived%'

Union All

SELECT 
        2 AS Tag, 
        1 AS Parent, 
        cmsPropertyData.contentNodeId as [node!1!id], 
        null, null, 
        cmsPropertyData.[id], 
        cmsPropertyType.Alias, 
        null, 
        isnull(Convert(Nvarchar(4000),dataInt), 
                isnull(Convert(Nvarchar(4000),dataDate),
                        isnull(Convert(Nvarchar(4000),dataNvarchar),
                                isnull(Convert(Nvarchar(4000),dataNtext),''))))

FROM 
        [Foodstuffs Suppliers].[dbo].umbracoNode
inner join [Foodstuffs Suppliers].[dbo].cmsPropertyData on umbracoNode.[id] = cmsPropertyData.contentNodeId
inner join [Foodstuffs Suppliers].[dbo].cmsPropertyType on cmsPropertyData.propertytypeid = cmsPropertyType.[id]
inner join [Foodstuffs Suppliers].[dbo].cmsMember on cmsMember.nodeId = umbracoNode.id

WHERE cmsPropertyType.contentTypeId = '1239' AND cmsMember.[loginName] NOT LIKE 'archived%' AND 
        [Foodstuffs Suppliers].[dbo].cmsPropertyType.Alias IN ('coyLegalEntityName','coyLegalTradingName','coyPhysical2',
        'coyPhysicalCity','coyAucklandVendorID','coyWellingtonVendorID','coySouthIslandVendorID',
        'coyPrimaryBusinessCategory','coySecondaryBusinessCategory',
        'foodstuffsBannerGroupCompanyParent','foodstuffsBannerGroupCompany')
ORDER BY [node!1!id], [data!2!id] 
FOR XML Explicit

Obviously there are custom member properties within the query and the member type ID's.

Perserverance paid off finally . . .

Umbraco rocks!

Nigel

is working on a reply...