Modifing XML document is too slow

I need insert many dates from tables to XML file, I use T-SQL, everything is OK, but it works too slow.

Is there another way? Thanks' a lot.

I use such sequence T-SQL:

Declare

@.doc XML,

@.ixml nvarchar(4000)

SELECT @.ixml=(SELECT * FROM SAMPLE "d:SAMPLE" for XML AUTO)

SET @.ixml = 'SET @.Doc.modify(''

declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";

declare namespace d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields";

insert ('+@.ixml+') into (dfs:myFields/dfs:dataFields)[1] '')'

exec sp_ExecuteSql @.STMT = @.ixml, @.PARAMS = N'@.DOC XML OUT', @.DOC = @.DOC OUT

In order for me to help you troubleshoot the performance please explain the shape of your XML data and the scenario you are using XML in.

General notes.

If your scenario is collecting changes made to a set of tables and aggregating them into an XML document you may want to aggregate each change into a smaller XML instance in a dedicated XML column and then aggregate multiple instances into a bigger XML document upon retrieval.

If your scenario is adding data from database tables to an existing document on the client side you may retrieve your data from the server in XML form using FOR XML and then combine the new and the existing XML documents using XSLT.

If you need to add data from database tables to an XML document stored in an XML variable/parameter the most performing will be to compose a new instance, for example:

WITH XMLNAMESPACES(

'http://schemas.microsoft.com/office/infopath/2003/dataFormSolution' AS dfs,

'http://schemas.microsoft.com/office/infopath/2003/ado/dataFields' AS d)

SELECT @.doc =

(

SELECT

@.doc.query('dfs:myFields/dfs:dataFields/*') AS "dfs:dataFields",

(SELECT * FROM SAMPLE FOR XML RAW('d:SAMPLE'), TYPE) AS "dfs:dataFields"

FOR XML PATH, TYPE, ROOT('dfs:myFields')

);

If reconstruction is too complex you can combine the old and new XML into a single instance, insert the new part into the appropriate place, and then delete the new part:

-- combine the old and the new XML

WITH XMLNAMESPACES(

'http://schemas.microsoft.com/office/infopath/2003/ado/dataFields' AS d)

SELECT @.doc =

(

SELECT

@.doc,

(SELECT * FROM SAMPLE FOR XML RAW('d:SAMPLE'), TYPE) AS "Wrapper"

FOR XML PATH, TYPE

);

-- insert the new XML fragment into the appropriate place

SET @.doc = @.doc.modify('

declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";

insert /Wrapper/* into (dfs:myFields/dfs:dataFields)[1]

');

-- delete the new XML fragment

SET @.doc = @.doc.modify('delete /Wrapper');

Inserting bigger XML fragments using dynamic query with modify(‘insert…’) method on XML type is not recommended in terms of performance.

Best regards,

Eugene Kogan,

Technical Lead,

SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

The code samples were not verified and may contain syntax errors.

|||

Thanks’ a lot.

I use completely another way, simple serializing with some handwriting end tags, time was decreased from 2 minutes 38 seconds to 7 seconds.

Modifing XML document is too slow

I need insert many dates from tables to XML file, I use T-SQL, everything is OK, but it works too slow.

Is there another way? Thanks' a lot.

I use such sequence T-SQL:

Declare

@.doc XML,

@.ixml nvarchar(4000)

SELECT @.ixml=(SELECT * FROM SAMPLE "d:SAMPLE" for XML AUTO)

SET @.ixml = 'SET @.Doc.modify(''

declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";

declare namespace d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields";

insert ('+@.ixml+') into (dfs:myFields/dfs:dataFields)[1] '')'

exec sp_ExecuteSql @.STMT = @.ixml, @.PARAMS = N'@.DOC XML OUT', @.DOC = @.DOC OUT

In order for me to help you troubleshoot the performance please explain the shape of your XML data and the scenario you are using XML in.

General notes.

If your scenario is collecting changes made to a set of tables and aggregating them into an XML document you may want to aggregate each change into a smaller XML instance in a dedicated XML column and then aggregate multiple instances into a bigger XML document upon retrieval.

If your scenario is adding data from database tables to an existing document on the client side you may retrieve your data from the server in XML form using FOR XML and then combine the new and the existing XML documents using XSLT.

If you need to add data from database tables to an XML document stored in an XML variable/parameter the most performing will be to compose a new instance, for example:

WITH XMLNAMESPACES(

'http://schemas.microsoft.com/office/infopath/2003/dataFormSolution' AS dfs,

'http://schemas.microsoft.com/office/infopath/2003/ado/dataFields' AS d)

SELECT @.doc =

(

SELECT

@.doc.query('dfs:myFields/dfs:dataFields/*') AS "dfs:dataFields",

(SELECT * FROM SAMPLE FOR XML RAW('d:SAMPLE'), TYPE) AS "dfs:dataFields"

FOR XML PATH, TYPE, ROOT('dfs:myFields')

);

If reconstruction is too complex you can combine the old and new XML into a single instance, insert the new part into the appropriate place, and then delete the new part:

-- combine the old and the new XML

WITH XMLNAMESPACES(

'http://schemas.microsoft.com/office/infopath/2003/ado/dataFields' AS d)

SELECT @.doc =

(

SELECT

@.doc,

(SELECT * FROM SAMPLE FOR XML RAW('d:SAMPLE'), TYPE) AS "Wrapper"

FOR XML PATH, TYPE

);

-- insert the new XML fragment into the appropriate place

SET @.doc = @.doc.modify('

declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";

insert /Wrapper/* into (dfs:myFields/dfs:dataFields)[1]

');

-- delete the new XML fragment

SET @.doc = @.doc.modify('delete /Wrapper');

Inserting bigger XML fragments using dynamic query with modify(‘insert…’) method on XML type is not recommended in terms of performance.

Best regards,

Eugene Kogan,

Technical Lead,

SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

The code samples were not verified and may contain syntax errors.

|||

Thanks’ a lot.

I use completely another way, simple serializing with some handwriting end tags, time was decreased from 2 minutes 38 seconds to 7 seconds.

Modelling Active Directory Data

Hello,
I am not sure if this is the right forum, but here goes.
I need to document the members of about 1000 Active Directory Group in a SQL
database. The groups are all in the same OU. I also need to document the
nested members (both groups and users). Putting the root groups in a table i
s
no problem. I am having trouble with maintaining the relationships between
the root groups\nested groups\nested members. A typical example would be:
Root group
Nested Group 1
Nested User 1
Nested User 2
Nested Group 2
Nested User 1
Nested User 2
Thanks in advance for any suggestions.Hi
You may want to check previous ADSI threads and the ADSI newsgroup such as
http://tinyurl.com/e7vtb
John
"bxr222" wrote:

> Hello,
> I am not sure if this is the right forum, but here goes.
> I need to document the members of about 1000 Active Directory Group in a S
QL
> database. The groups are all in the same OU. I also need to document the
> nested members (both groups and users). Putting the root groups in a table
is
> no problem. I am having trouble with maintaining the relationships between
> the root groups\nested groups\nested members. A typical example would be:
> Root group
> Nested Group 1
> Nested User 1
> Nested User 2
> Nested Group 2
> Nested User 1
> Nested User 2
> Thanks in advance for any suggestions.
>|||John,
Thanks for responding. I have seen similar info before but do not think this
suits my needs. Particularily, no info exists on getting the members of
groups. I can do this easily using VBScript, but need a table structure to
input it into. Additionally, I need to be able to track changes to these
groups on a quarterly basis. I may be overlooking something here though.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may want to check previous ADSI threads and the ADSI newsgroup such as
> http://tinyurl.com/e7vtb
> John
> "bxr222" wrote:
>|||Hi
The recursion is best handled in the script example, you can then decide on
what you wish to store in the database.
John
"bxr222" wrote:
[vbcol=seagreen]
> John,
> Thanks for responding. I have seen similar info before but do not think th
is
> suits my needs. Particularily, no info exists on getting the members of
> groups. I can do this easily using VBScript, but need a table structure to
> input it into. Additionally, I need to be able to track changes to these
> groups on a quarterly basis. I may be overlooking something here though.
> "John Bell" wrote:
>

Modelling Active Directory Data

Hello,
I am not sure if this is the right forum, but here goes.
I need to document the members of about 1000 Active Directory Group in a SQL
database. The groups are all in the same OU. I also need to document the
nested members (both groups and users). Putting the root groups in a table is
no problem. I am having trouble with maintaining the relationships between
the root groups\nested groups\nested members. A typical example would be:
Root group
Nested Group 1
Nested User 1
Nested User 2
Nested Group 2
Nested User 1
Nested User 2
Thanks in advance for any suggestions.Hi
You may want to check previous ADSI threads and the ADSI newsgroup such as
http://tinyurl.com/e7vtb
John
"bxr222" wrote:
> Hello,
> I am not sure if this is the right forum, but here goes.
> I need to document the members of about 1000 Active Directory Group in a SQL
> database. The groups are all in the same OU. I also need to document the
> nested members (both groups and users). Putting the root groups in a table is
> no problem. I am having trouble with maintaining the relationships between
> the root groups\nested groups\nested members. A typical example would be:
> Root group
> Nested Group 1
> Nested User 1
> Nested User 2
> Nested Group 2
> Nested User 1
> Nested User 2
> Thanks in advance for any suggestions.
>|||John,
Thanks for responding. I have seen similar info before but do not think this
suits my needs. Particularily, no info exists on getting the members of
groups. I can do this easily using VBScript, but need a table structure to
input it into. Additionally, I need to be able to track changes to these
groups on a quarterly basis. I may be overlooking something here though.
"John Bell" wrote:
> Hi
> You may want to check previous ADSI threads and the ADSI newsgroup such as
> http://tinyurl.com/e7vtb
> John
> "bxr222" wrote:
> > Hello,
> >
> > I am not sure if this is the right forum, but here goes.
> >
> > I need to document the members of about 1000 Active Directory Group in a SQL
> > database. The groups are all in the same OU. I also need to document the
> > nested members (both groups and users). Putting the root groups in a table is
> > no problem. I am having trouble with maintaining the relationships between
> > the root groups\nested groups\nested members. A typical example would be:
> >
> > Root group
> > Nested Group 1
> > Nested User 1
> > Nested User 2
> > Nested Group 2
> > Nested User 1
> > Nested User 2
> >
> > Thanks in advance for any suggestions.
> >|||Hi
The recursion is best handled in the script example, you can then decide on
what you wish to store in the database.
John
"bxr222" wrote:
> John,
> Thanks for responding. I have seen similar info before but do not think this
> suits my needs. Particularily, no info exists on getting the members of
> groups. I can do this easily using VBScript, but need a table structure to
> input it into. Additionally, I need to be able to track changes to these
> groups on a quarterly basis. I may be overlooking something here though.
> "John Bell" wrote:
> > Hi
> >
> > You may want to check previous ADSI threads and the ADSI newsgroup such as
> > http://tinyurl.com/e7vtb
> >
> > John
> >
> > "bxr222" wrote:
> >
> > > Hello,
> > >
> > > I am not sure if this is the right forum, but here goes.
> > >
> > > I need to document the members of about 1000 Active Directory Group in a SQL
> > > database. The groups are all in the same OU. I also need to document the
> > > nested members (both groups and users). Putting the root groups in a table is
> > > no problem. I am having trouble with maintaining the relationships between
> > > the root groups\nested groups\nested members. A typical example would be:
> > >
> > > Root group
> > > Nested Group 1
> > > Nested User 1
> > > Nested User 2
> > > Nested Group 2
> > > Nested User 1
> > > Nested User 2
> > >
> > > Thanks in advance for any suggestions.
> > >

Mobile vs Compact - What changed?

What really changed from SQL Mobile to SQL Compact besides allowing the database to run on any desktop? Is there a document somewhere explaining the differences, I did not see anything?

I just changed my development machine over to SP1 and wondered what code I should be changing.

I am filling the database from my C++ Desktop app using ADO using the OLEDB provider Microsoft.SQLSERVER.MOBILE.OLEDB.3.0 and using System.Data.SqlServerCe in my C# mobile app. Is that OLE provider valid with the new version or should it be *.COMPACT.3.1?

I thought there was now suppose to be some sort of automatic compaction, but I did not notice any new properties.

I would appreciate any insight into the changes.

Thanks,

Juan Foegen

From my understanding, SQL Server Compact edition is an updated version to SQL Server Mobile Edition. You can use the compact edition not only on the mobile devices, but also on various devices like Tablet PC's, desktop computers etc. Also they are trying to include lot of sync features in the future releases, as you can see now they released Sync Services for ADO.NET CTP, the next new sync model like Merge Replication and RDA and in the next version release of SQL Server Compact Framework 3.5 they are trying to include all these features.

You can visit http://blogs.msdn.com/stevelasker/ for more information on these changes.

However, the namespace System.Data.SqlServerCE is going to be same for all the releases as it was same in both SQL Server CE and SQL Server Mobile.

Thanks

Ravi.

|||The OLEDB provider has not changed name, as the 3.1 release mainly concerns lifting the restrictions on supported platforms. For detailed information on the new features and fixes in 3.1 (or 3.0.5300.0) see this kb article: http://support.microsoft.com/Default.aspx?kbid=920700|||

Thanks for all your help and other replies on this forum.

Mobile vs Compact - What changed?

What really changed from SQL Mobile to SQL Compact besides allowing the database to run on any desktop? Is there a document somewhere explaining the differences, I did not see anything?

I just changed my development machine over to SP1 and wondered what code I should be changing.

I am filling the database from my C++ Desktop app using ADO using the OLEDB provider Microsoft.SQLSERVER.MOBILE.OLEDB.3.0 and using System.Data.SqlServerCe in my C# mobile app. Is that OLE provider valid with the new version or should it be *.COMPACT.3.1?

I thought there was now suppose to be some sort of automatic compaction, but I did not notice any new properties.

I would appreciate any insight into the changes.

Thanks,

Juan Foegen

From my understanding, SQL Server Compact edition is an updated version to SQL Server Mobile Edition. You can use the compact edition not only on the mobile devices, but also on various devices like Tablet PC's, desktop computers etc. Also they are trying to include lot of sync features in the future releases, as you can see now they released Sync Services for ADO.NET CTP, the next new sync model like Merge Replication and RDA and in the next version release of SQL Server Compact Framework 3.5 they are trying to include all these features.

You can visit http://blogs.msdn.com/stevelasker/ for more information on these changes.

However, the namespace System.Data.SqlServerCE is going to be same for all the releases as it was same in both SQL Server CE and SQL Server Mobile.

Thanks

Ravi.

|||The OLEDB provider has not changed name, as the 3.1 release mainly concerns lifting the restrictions on supported platforms. For detailed information on the new features and fixes in 3.1 (or 3.0.5300.0) see this kb article: http://support.microsoft.com/Default.aspx?kbid=920700|||

Thanks for all your help and other replies on this forum.

sql