FOR XML EXPLICIT doesnt seem to work for me--HELP NEEDED ASAP!!

Hi,
Am trying to produce XML output using "FOR XML EXPLICIT" using a dynamically created table. Say ur using the Northwind DB, Customers table. Is there a way i can have each column displayed in a different row such that the attribute is column name and the text element is the column value e.g.

<Customers>
<Customer>
<Field fieldname="ContactName">Alfreds Futterkiste</Field>
<Field fieldname="ContactTitle">Sales Representative</Field>
</Customer>
<Customer>
...

</Customer>
</Customers>

Basically that's the format i need for the output for whichever customer(s) get retrieved from the table. All methods i've thought of havent worked upto now so in case someone has an idea please feel free to share the code. Thanx in adv!

My apologies for the delay in answering....

If you know the names of the fields you could do it. But not if you don't. Are you using SQL Server 2000 or 2005? In 2005 the query formulation becomes quite a bit simpler using FOR XML PATH...

Here is the EXPLICIT solution:

select 1 as tag, NULL as parent
, 1 as "Customers!1!!hide"
, NULL as "Customer!2!!hide"
, NULL as "Field!3!fieldname", NULL as "Field!3!"

union all
select 2 , 1
, 1
, CustomerID
, NULL, NULL
from Customers

union all
select 3 , 2
, 1
, CustomerID
, 'ContactName', ContactName
from Customers

union all
select 3 , 2
, 1
, CustomerID
, 'ContactTitle', ContactTitle
from Customers

-- more for other fields
order by "Customers!1!!hide", "Customer!2!!hide"
for xml explicit

and here the 2005 FOR XML PATH one (note that the '' is needed to break it into two elements):

select 'ContactName' as "Field/@.fieldname", ContactName as "Field", ''
, 'ContactTitle' as "Field/@.fieldname", ContactTitle as "Field"
from Customers
for xml path('Customer'), ROOT('Customers')

Best regards
Michael

For XML data retrieval

How to add line breaks after each node when I use "for xml explicit"? I would appreciate it for any suggestions.

If you are using Query Analyzer with SQL Server 2000

executing "dbcc traceon(257)" will cause XML generated with

FOR XML to be displayed with line breaks.

|||

Mark,

Thank you so much! I feel I almost get there. I use dbcc traceon(257) and see the link breaks. I have a question to save the file?

I have sql script that return xml via "FOR XML EXPLICIT" clause. I can specify output to file in Query Analyzer. Is it possible to save output file as file extension .ctl? I would like to send you the file format to you if it could save your time and understand better for the file format I want. My email address is fancorning4@.yahoo.com

Thank you again

For security reasons DTD is prohibited in this XML document and System.OutOfMemory

I am getting this error while running a very large dataset. Please help..

The full description of the error is : "For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method."

I am having hard time figuring out a solution for this. I am using sql 05, SSRS 05, Report viewer

I have a very big Dataset which pulls up millions of rows.

When I pass parameters from Windows forms to run on Reportviewer through webservice it is giving me "System.OutOfMemory" Exception and when I pass same parameters on the server(i.e. http:\\server\reportmanager) it is giving me this error "For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method".

I have no problem when I pass small dataset.

Please help me solve this annoying this problem.

|||

I found this article and i guess this should answer your question.

http://support.microsoft.com/default.aspx/kb/909678

|||

Thanks Chaitanya for the informative link.

I've already checked that and done necessary changes for the long running reports.

Right now I was able to print 55541 pages. We decided to take printout in batches if the requirement more than 55541. They best possible solution would be schedule a report and call it from Windows service using webservices.

Please let me know if we can print unlimited pages(i.e about 3 million pages).

For security reasons DTD is prohibited in this XML document

I am getting this error while running a very large dataset. Please help..

The full description of the error is : "For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method."

I am having hard time figuring out a solution for this. I am using sql 05, SSRS 05, Report viewer

I have a very big Dataset which pulls up millions of rows.

When I pass parameters from Windows forms to run on Reportviewer through webservice it is giving me "System.OutOfMemory" Exception and when I pass same parameters on the server(i.e. http:\\server\reportmanager) it is giving me this error "For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method".

I have no problem when I pass small dataset.

Please help me solve this annoying this problem.

|||

I found this article and i guess this should answer your question.

http://support.microsoft.com/default.aspx/kb/909678

|||

Thanks Chaitanya for the informative link.

I've already checked that and done necessary changes for the long running reports.

Right now I was able to print 55541 pages. We decided to take printout in batches if the requirement more than 55541. They best possible solution would be schedule a report and call it from Windows service using webservices.

Please let me know if we can print unlimited pages(i.e about 3 million pages).

For Loop

Hello,

Please, could someone explain me how "For Loop" funtions?

because I need to load data depending on a variable?

Can I use For loop to do that?

thanks

Your question is a bit vague. Here is the For Loop documentation. Yes, you can use variables to control the loop. If this doesn't answer your question, then please be more specific about what you're trying to do.

For Each from Variable Enumerator

Hi ,

Can someone help me in using the "For each from variable enumerator".

The work around that I have made so far is

1) Generating a Enumerated Value of File collection using Script Control

2) A For each Collection container that processes a given set of files.

the above two conditions works for me when executed independently. But I am not able to relate these two, so as to give a enumerator input to the "For each from Variable Enumerator"

any help will be highly appreciated...Thanks

For each from variable enumerator can enumerate over variable that contains .NET collection, e.g. ArrayList. Using it is a bit complex, let's go step by step:

First, create a variable that will contain the collection, let's call it User::Collection, set variable type to Object.

Now populate it in Script Task: create Script Task, specify that it uses this variable for read/write, add a code like this inside Main function:

Dim arr As New ArrayList
arr.Add("D:\Tests\a.txt")
arr.Add("D:\Tests\b.txt")
Dts.Variables("User::Collection").Value = arr

Now add a ForEach loop, specify that it uses Foreach From Variable enumerator, and the variable is User::Collection.

Switch to Variable mapping page of Foreach Loop Editor. We'll need another variable to hold the current value of the enumerator, so click a cell under Variable, select a <New Variable>, create a variable called EnumValue. Leave it associated with Index 0 (our enuration value has only one value).

OK, now add something inside the loop to be executed for each iteration. I choosed File System task, to copy files from the enumerated value to directory c:\tests\destination folder. You can use other tasks, e.g. Data Flow that uses the file in a Flat File Source Adapter.

Open File System task editor. Set operation to CopyFiles. Create source connection (using any file, we'll change it anyway later), destination connection to destination folder. Close the File System task editor.

Now we need to tell the connection manager to use our enumerated value. Select the source connection manager. In Properties panel, select Expressions property and click '...'. Select property ConnectionString, click '...' and drag the EnumValue variable to expression field. The expression should be @.[User::EnumValue].

Finally, we need to set DelayValidation property of File System task to TRUE. The reason is that since the source connection manager gets the value from the variable, we should not validate the task in the beginning of the package.

OK, now run the package! The files added in the script task should get copied to destination folder.