ForEachLoop Container and Variables

Hi Guys

I am trying to do the following and am quite new to SSIS.

I have to select a dataset from a database on server A, check if it exists on server B and perform an Update or Insert dependant on the existence.

I have created a SQL task to do the Select from server A with the results set passed to a variable of Vendors. I have added a ForEach Loop container with an enumerator of Foreach ADO Enumerator and the source variable is set to Vendors.

I have created 2 variables in the Foreach Loop called Code and Supplier - both as strings - as there are 2 fields from the initial Select that need to be passed to the final Update/ Insert.

I have then created another SQL task insert the Foreach which will perform the Update/Insert.

obviously when I run it at the moment it performs the Update/ Insert but just adds the rows with both Code and Supplier as NULL.

having looked at a couple of examples in books I have i know i need to add something in the Expressions of the Update/Insert SQL task but it is here i get a bit lost.

Which of the properties from the drop down do i need to use to map the variables against?

Any help would be massively appreciated asI am tearing my hair out!

Thanks

Scott

Hi Scott,

We're all still learning SSIS.

It sounds like you're most of the way there.

There are a couple ways to approach this solution. The simplest way, from what I understand from your post, is to use placeholders and parameters in your Update/Insert statements. If you already have the Code and Supplier variables defined, you could perform an insert using an Execute SQL Task with something similar to the following code:

Code Snippet

INSERT INTO Vendors

(Code, Supplier)

VALUES(?, ?)

You could then supply Parameters:

Code Snippet

VariableName Direction DataType ParameterName ParameterSize

User::Code Input Int 0 -1

User::Supplier Input VarChar 1 -1

This would substitute the question marks in the SQL Statement property with the values contained in your variables.

Hope this helps,

Andy

|||

Scott,

Any special reason for not using a dataflow with a lookup transform to detect if the rows exists(update) or not (insert). That is by far a pretty common practice in these scenarios.

|||

Hi Rafael

Still new to this (and database stuff as a whole) and am going on someone elses advice!

I have looked at your suggestion and have got as far as the following:

OLEDB Source with a SQL select statement to return the data required

Look Up transform to look up the 2 columns from the Select against the destination table

After that I am a bit lost. I guess i have to add a OLEDB destination but do I do it to a table or a SQL Command?

thanks again

Scott

|||

I think you are on the right track. I would add an OLE DB Destination against the destination table.

Keep in mind you have to tweak the lookup to 'redirect' errors. Lookup will treat the no matches as errors; hence will be send to the error output of the component (red arrow). Then you have to connect the error output of the Lup to the input of the destination.

Now the updates; every row going to the green output of the L.up is an existing/to-updated row. Here you have 2 options; use an OLE DB Commnad to update the row in the destination table; or send those rows to an estiging table (yes a seconf OLE DB Destination) and then back in control flow use an Execute SQl task to do a 1 time update. The advantage of the second method is performance. the Update runs 1 time updating all the required rows. The First one will perform an update for every row passing trhough; wich depending on the volume of data can be performance killer; the good thing is that you don't need a second table.

This thread has some examples

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1

Foreach Loop read table data and write to file

Hi,

I want to do the following with a ssis package:

INPUT:

A table contains 2 columns with data i need. column A=Filename and column B=FileContent

PROCESS:

I need to loop through ea record in the table and retrieve columns A and B. Then for ea column i need to write the Content hold in column B into File hold in column A.

I so far found out, that i need a Execute SQL Task in Control Flow querying the table and get columns A and B into 2 variables, plus a 3rd var holding the object. Then the output goes into a Foreach Loop Container. From this point i don't know how to continue. I tried to put a Data Flow Task inside the Foreach Loop, but couldn't find out how i now get the 2 variables to the Data Flow Task and use them to for the file to be written and the content to be placed in the file.

Is there any example similiar to that so i could learn how to start on that?

Thanks

Danny

(Further you can use Import Column transform; in example from here this transform was called File Inserter (in beta release).) - I thought you need insert a file. To export a file you need Export Column transform

|||

The Sample you mention is not exactly what i need. That sample loops through a list of files and writes the names of the files back to a table. Then it has a standard Data Flow Task reading the table with the filenames inserted before and do something with it.

What i need is loops through a table, and for each row i need 2 values from the table to work with in the Data Flow Task. One of the values is the filename to be written and the other value is the content to be written in the file.

|||

You can do in following way :

1. Let's say you want to put the files in c:\YourFolder, add a data flow task and connection to your table

2. Add a derived column transformation; make a derived column name NewFilePath and in expressions :

"C:\\YourFolder\\+(DT_WSTR,50)ColumnA"

3. Add an Export Column transformation; in Export Column transformation editor set

Extract Column= ColumnB

File Path Column=NewFilePath

so SSIS will get the file from columnB and put in the folder using NewFilePath

Foreach Loop and distributed files

Hi - I'm new to SSIS and am having problems figuring out how to do the following.

I need to load data from flat files into SQLserver 2005 and have created the data flows ok, but my data files are *not* located in a single directory so I cannot use the foreach file enumerator option in the foreach loop container collection. Please correct me if I'm wrong?

My approach has been to execute a SQLcommand to get the filenames from another database table and to use the foreach ADO enumerator option and mapping the returned filenames to a project scoped variable (data type object since it is a rowset).

My problem comes when I edit the properties of the connection manager to try to use that variable for the connectionstring property in the expression editor. I get an error because the datatype of the variable is not supported in an expression.

Can anyone tell me how to correct this or outline another way to solve my problem?

thanks

Brian McLean wrote:

I get an error because the datatype of the variable is not supported in an expression.

Why not? You should be posting the result of the foreach loop into a string variable.|||

But you cannot return a recordset into a string! I tried and the sql execution failed with the following error...

Error: 0xC001F009 at DAOphotLoad: The type of the value being assigned to variable "User::FileList" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Select Catalog files from HLA DB, Execute SQL Task: Executing the query "Select DAOcat_filename from ImgFileInfo where DAOCat_status like '%Processed%'" failed with the following error: "The type of the value being assigned to variable "User::FileList" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

|||You return a recordset into an object typed variable. Then the foreach loop works on that object variable. Using the variable mappings on the foreach loop, you can store the pieces of data in the object variable in string, int, whatver, variables.|||

Brian McLean wrote:

My approach has been to execute a SQLcommand to get the filenames from another database table and to use the foreach ADO enumerator option and mapping the returned filenames to a project scoped variable (data type object since it is a rowset).

You are in the right track; but you are missing one part; you need to shred the rowset into string variables:

Jamie has a sample package here; pay special attention to Collection and Variable mapping tabs inside of the forEach loop container:

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx

Forcing Query Plans

Is it possible to force a query plan on a Stored procedue. I have attempted the following and i receive a Incorrect syntax near the keyword 'OPTION'. Any ideas?

EXEC testdatabases..testprocedure

OPTION (USE PLAN N'
<ShowPlanXML xmlns=
"http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="0.5"
Build="9.00.1187.07">
<BatchSequence>
<Batch>
<Statements>
...
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
')
GO

You could use OPTION and USE PLAN only with SELECT/INSERT/DELETE/STATEMENTS. So move your plan in body of stored procedures.

If you couldn't change your stored procedure use Plan Guide by sp_create_plan_guide http://msdn2.microsoft.com/en-us/library/ms179880.aspx

Force server use 3GB memory

Hi,
We have AWE enabled but SQL Server 2000 with SP3 only use
103 MB memory during peak-hour. So we try to force SQL use
3GB memory. Is the following statements' order all right?
sp_configure 'max server memory', 3072
reconfigure with override
go
exec sp_configure 'min server memory', 3072
reconfigure with override
go
exec sp_configure 'show advanced options', '1'
reconfigure with override
go
exec sp_configure 'set working set size', 1
reconfigure with override
go
then re-start SQL Server.
Thanks!
JennyHow are you monitoring the memory, Task manager doesn't understand AWE so it
reports incorrect figures. Perfmon counters will be accurate, use the Total
Server Memory (KB) counter. You don't need to set min memory or working
size, SQL will grab the memory upto max server memory at startup with AWE
enabled (thus is a good idea to set max server memory like you have
otherwise it only leaves 128MB for the OS). Having said all that, it might
be better using the /3GB switch rather than AWE assuming you have <12GB
total server memory if you only want 3 GB. I am assuming you are using
SQL2000 EE and win2kAS or wink3 EE.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jenny" <jyu@.iseoptions.com> wrote in message
news:017901c39ff2$3450fa10$a601280a@.phx.gbl...
Hi,
We have AWE enabled but SQL Server 2000 with SP3 only use
103 MB memory during peak-hour. So we try to force SQL use
3GB memory. Is the following statements' order all right?
sp_configure 'max server memory', 3072
reconfigure with override
go
exec sp_configure 'min server memory', 3072
reconfigure with override
go
exec sp_configure 'show advanced options', '1'
reconfigure with override
go
exec sp_configure 'set working set size', 1
reconfigure with override
go
then re-start SQL Server.
Thanks!
Jennysql

FOR XML: date returned without timezone

I'm querying a table with a datetime field using SELECT ... FOR XML. The
dates are returned in the following format:
<updated>2006-01-04T11:52:52.403</updated>
Is there any possiblity to add the timezone information to it? The result
should look as follows:
<updated>2006-01-04T11:52:52.403Z</updated>
The SQL statements looks like
SELECT date AS updated
FROM articles FOR XML PATH('entry')
Best regards
MartinI found a solution:
CONVERT(VARCHAR, date, 126) + 'Z' AS updated
126 is the style code for the ISO8601 date representation, i.e. XML
representation.
"Martin Szugat" wrote:

> I'm querying a table with a datetime field using SELECT ... FOR XML. The
> dates are returned in the following format:
> <updated>2006-01-04T11:52:52.403</updated>
> Is there any possiblity to add the timezone information to it? The result
> should look as follows:
> <updated>2006-01-04T11:52:52.403Z</updated>
> The SQL statements looks like
> SELECT date AS updated
> FROM articles FOR XML PATH('entry')
> Best regards
> Martin

FOR XML: date returned without timezone

I'm querying a table with a datetime field using SELECT ... FOR XML. The
dates are returned in the following format:
<updated>2006-01-04T11:52:52.403</updated>
Is there any possiblity to add the timezone information to it? The result
should look as follows:
<updated>2006-01-04T11:52:52.403Z</updated>
The SQL statements looks like
SELECT date AS updated
FROM articles FOR XML PATH('entry')
Best regards
Martin
I found a solution:
CONVERT(VARCHAR, date, 126) + 'Z' AS updated
126 is the style code for the ISO8601 date representation, i.e. XML
representation.
"Martin Szugat" wrote:

> I'm querying a table with a datetime field using SELECT ... FOR XML. The
> dates are returned in the following format:
> <updated>2006-01-04T11:52:52.403</updated>
> Is there any possiblity to add the timezone information to it? The result
> should look as follows:
> <updated>2006-01-04T11:52:52.403Z</updated>
> The SQL statements looks like
> SELECT date AS updated
> FROM articles FOR XML PATH('entry')
> Best regards
> Martin

for xml to local variable

Is there a reason why the following does not work...
declare @.s varchar(1024)
set @.s = (select * from validTable for xml auto)
...assuming I know for a fact that the returned xml stream will fit inside my declared variable.
I suspect it's just the pre-compiler getting it's knickers in a knot over the * when this technically meets the requirements for setting a local variable.
... i think...
Does any one have any comments or ideas on how to achieve the same effect inside an SQL stored Proc.?
WR
Unfortunately, this can't be done in SQL Server 2000 - what actually gets
returned is a single column/single row resultset containing the XML stream.
The client-side components of SQLXML can extract that as a stream but
there's no way to do it in T-SQL.
In SQL Server 2005, you can use the xml data type to do what you're
suggesting.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"WildRide" <WildRide@.discussions.microsoft.com> wrote in message
news:06CEF1F3-F947-45B9-93BE-BE8FEF4E3DF6@.microsoft.com...
Is there a reason why the following does not work...
declare @.s varchar(1024)
set @.s = (select * from validTable for xml auto)
...assuming I know for a fact that the returned xml stream will fit inside
my declared variable.
I suspect it's just the pre-compiler getting it's knickers in a knot over
the * when this technically meets the requirements for setting a local
variable.
... i think...
Does any one have any comments or ideas on how to achieve the same effect
inside an SQL stored Proc.?
WR
|||sorry but i have testing this code with sqlserver 2000
and it doesent work
Cdlt
Query:
declare @.s varchar(1024)
set @.s = (select * from USERPROFILE for xml auto)
Result:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'xml'.

>--Original Message--
>Unfortunately, this can't be done in SQL Server 2000 -
what actually gets
>returned is a single column/single row resultset
containing the XML stream.
>The client-side components of SQLXML can extract that as
a stream but
>there's no way to do it in T-SQL.
>In SQL Server 2005, you can use the xml data type to do
what you're
>suggesting.
>Cheers,
>Graeme
>--
>--
>Graeme Malcolm
>Principal Technologist
>Content Master Ltd.
>www.contentmaster.com
>www.microsoft.com/mspress/books/6137.asp
>
>"WildRide" <WildRide@.discussions.microsoft.com> wrote in
message
>news:06CEF1F3-F947-45B9-93BE-BE8FEF4E3DF6@.microsoft.com...
>Is there a reason why the following does not work...
>declare @.s varchar(1024)
>set @.s = (select * from validTable for xml auto)
>...assuming I know for a fact that the returned xml
stream will fit inside
>my declared variable.
>I suspect it's just the pre-compiler getting it's
knickers in a knot over
>the * when this technically meets the requirements for
setting a local
>variable.
>... i think...
>Does any one have any comments or ideas on how to achieve
the same effect
>inside an SQL stored Proc.?
>WR
>
>.
>
|||"Boss Hog" <anonymous@.discussions.microsoft.com> wrote in message
news:74c001c4764f$9c4d6bb0$a301280a@.phx.gbl...
> sorry but i have testing this code with sqlserver 2000
> and it doesent work
It won't work because it isn't supported.
Bryant

FOR XML PATH nested query refer to parent variable

I have the following query:
Select BunCode as '@.BunCode', Min(TradingDate) as '@.TradingDate',
(Select ItemQty, TransactionSequenceNumber, GsiCode, RetalItemCode
FROM ods_TransmitFile WHERE BunCode = 1821
FOR XML PATH, TYPE)
FROM ods_TransmitFile
GROUP BY BunCode
ORDER BY BunCode
FOR XML PATH('BunCode'), ROOT('DM')
The query returns the correct structure however I would like the nested
query to refer to the '@.BunCode' of the parent query.
At present the nested results will always be for BunCode 1821. How can
I set ...WHERE BunCode = @.BunCode to equal the BunCode of the current
element?
Hope this makes sense?
Maybe by aliasing the tables? NOTE: *Untested*:
Select otf2.BunCode as '@.BunCode', Min(otf2.TradingDate) as '@.TradingDate',
(Select otf1.ItemQty, otf1.TransactionSequenceNumber, otf1.GsiCode,
otf1.RetalItemCode
FROM ods_TransmitFile otf1 WHERE otf1.BunCode = otf2.BunCode
FOR XML PATH, TYPE)
FROM ods_TransmitFile otf2
GROUP BY otf2.BunCode
ORDER BY otf2.BunCode
FOR XML PATH('BunCode'), ROOT('DM')
<justin.drerup@.gmail.com> wrote in message
news:1166543884.982800.201900@.t46g2000cwa.googlegr oups.com...
>I have the following query:
> Select BunCode as '@.BunCode', Min(TradingDate) as '@.TradingDate',
> (Select ItemQty, TransactionSequenceNumber, GsiCode, RetalItemCode
> FROM ods_TransmitFile WHERE BunCode = 1821
> FOR XML PATH, TYPE)
> FROM ods_TransmitFile
> GROUP BY BunCode
> ORDER BY BunCode
> FOR XML PATH('BunCode'), ROOT('DM')
> The query returns the correct structure however I would like the nested
> query to refer to the '@.BunCode' of the parent query.
> At present the nested results will always be for BunCode 1821. How can
> I set ...WHERE BunCode = @.BunCode to equal the BunCode of the current
> element?
> Hope this makes sense?
>
|||Thanks for your reply, I managed to get the solution working using
table aliases in a similar fashion to what you suggested.
|||Thanks for your reply, I managed to get the solution working using
table aliases in a similar fashion to what you suggested.

FOR XML PATH nested query refer to parent variable

I have the following query:
Select BunCode as '@.BunCode', Min(TradingDate) as '@.TradingDate',
(Select ItemQty, TransactionSequenceNumber, GsiCode, RetalItemCode
FROM ods_TransmitFile WHERE BunCode = 1821
FOR XML PATH, TYPE)
FROM ods_TransmitFile
GROUP BY BunCode
ORDER BY BunCode
FOR XML PATH('BunCode'), ROOT('DM')
The query returns the correct structure however I would like the nested
query to refer to the '@.BunCode' of the parent query.
At present the nested results will always be for BunCode 1821. How can
I set ...WHERE BunCode = @.BunCode to equal the BunCode of the current
element?
Hope this makes sense?Maybe by aliasing the tables? NOTE: *Untested*:
Select otf2.BunCode as '@.BunCode', Min(otf2.TradingDate) as '@.TradingDate',
(Select otf1.ItemQty, otf1.TransactionSequenceNumber, otf1.GsiCode,
otf1.RetalItemCode
FROM ods_TransmitFile otf1 WHERE otf1.BunCode = otf2.BunCode
FOR XML PATH, TYPE)
FROM ods_TransmitFile otf2
GROUP BY otf2.BunCode
ORDER BY otf2.BunCode
FOR XML PATH('BunCode'), ROOT('DM')
<justin.drerup@.gmail.com> wrote in message
news:1166543884.982800.201900@.t46g2000cwa.googlegroups.com...
>I have the following query:
> Select BunCode as '@.BunCode', Min(TradingDate) as '@.TradingDate',
> (Select ItemQty, TransactionSequenceNumber, GsiCode, RetalItemCode
> FROM ods_TransmitFile WHERE BunCode = 1821
> FOR XML PATH, TYPE)
> FROM ods_TransmitFile
> GROUP BY BunCode
> ORDER BY BunCode
> FOR XML PATH('BunCode'), ROOT('DM')
> The query returns the correct structure however I would like the nested
> query to refer to the '@.BunCode' of the parent query.
> At present the nested results will always be for BunCode 1821. How can
> I set ...WHERE BunCode = @.BunCode to equal the BunCode of the current
> element?
> Hope this makes sense?
>|||Thanks for your reply, I managed to get the solution working using
table aliases in a similar fashion to what you suggested.|||Thanks for your reply, I managed to get the solution working using
table aliases in a similar fashion to what you suggested.

FOR XML PATH

Hi,
I have the following query:
SELECT
C.CompanyName,
O.OrderDate,
E.LastName AS 'Registered By'
FROM Customers C
JOIN Orders O
ON C.CustomerID=O.CustomerID
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
ORDER BY C.CompanyName
I need all information of orders for particular company, appear under the
element of that company but the query that I have written returns the name
of company again and again for each order:
SELECT
C.CompanyName AS "@.Company",
O.OrderDate AS "Order/OrderDate",
E.LastName AS "Order/RegisteredBy"
FROM Customers C
JOIN Orders O
ON C.CustomerID=O.CustomerID
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
ORDER BY C.CompanyName
FOR XML PATH
Any help would be greatly appreciated.
Leila
oops! seems that the problem solved:
SELECT
C.CompanyName AS "@.Company",
(SELECT
O.OrderDate AS 'Order/OrderDate',
E.LastName AS 'Order/RegisteredBy'
FROM Orders O
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
WHERE O.CustomerID=C.CUstomerID
FOR XML PATH(''),type)
FROM Customers C
ORDER BY C.CompanyName
FOR XML PATH('Customers'),ROOT('ROOT')
Thanks to BOL documentation team :-)
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23AjU8w2IGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following query:
> SELECT
> C.CompanyName,
> O.OrderDate,
> E.LastName AS 'Registered By'
> FROM Customers C
> JOIN Orders O
> ON C.CustomerID=O.CustomerID
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> ORDER BY C.CompanyName
>
> I need all information of orders for particular company, appear under the
> element of that company but the query that I have written returns the name
> of company again and again for each order:
> SELECT
> C.CompanyName AS "@.Company",
> O.OrderDate AS "Order/OrderDate",
> E.LastName AS "Order/RegisteredBy"
> FROM Customers C
> JOIN Orders O
> ON C.CustomerID=O.CustomerID
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> ORDER BY C.CompanyName
> FOR XML PATH
> Any help would be greatly appreciated.
> Leila
>
>
|||Good documentation can help ;)
You can make your query slightly easier to maintain by writing the subquery
as
(SELECT
O.OrderDate AS 'OrderDate',
E.LastName AS 'RegisteredBy'
FROM Orders O
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
WHERE O.CustomerID=C.CUstomerID
FOR XML PATH('Order'),type)
Best regards
Michael
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23LnD5B3IGHA.3696@.TK2MSFTNGP15.phx.gbl...
> oops! seems that the problem solved:
> SELECT
> C.CompanyName AS "@.Company",
> (SELECT
> O.OrderDate AS 'Order/OrderDate',
> E.LastName AS 'Order/RegisteredBy'
> FROM Orders O
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> WHERE O.CustomerID=C.CUstomerID
> FOR XML PATH(''),type)
> FROM Customers C
> ORDER BY C.CompanyName
> FOR XML PATH('Customers'),ROOT('ROOT')
> Thanks to BOL documentation team :-)
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23AjU8w2IGHA.1676@.TK2MSFTNGP09.phx.gbl...
>

FOR XML PATH

Hi,
I have the following query:
SELECT
C.CompanyName,
O.OrderDate,
E.LastName AS 'Registered By'
FROM Customers C
JOIN Orders O
ON C.CustomerID=O.CustomerID
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
ORDER BY C.CompanyName
I need all information of orders for particular company, appear under the
element of that company but the query that I have written returns the name
of company again and again for each order:
SELECT
C.CompanyName AS "@.Company",
O.OrderDate AS "Order/OrderDate",
E.LastName AS "Order/RegisteredBy"
FROM Customers C
JOIN Orders O
ON C.CustomerID=O.CustomerID
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
ORDER BY C.CompanyName
FOR XML PATH
Any help would be greatly appreciated.
Leilaoops! seems that the problem solved:
SELECT
C.CompanyName AS "@.Company",
(SELECT
O.OrderDate AS 'Order/OrderDate',
E.LastName AS 'Order/RegisteredBy'
FROM Orders O
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
WHERE O.CustomerID=C.CUstomerID
FOR XML PATH(''),type)
FROM Customers C
ORDER BY C.CompanyName
FOR XML PATH('Customers'),ROOT('ROOT')
Thanks to BOL documentation team :-)
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23AjU8w2IGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following query:
> SELECT
> C.CompanyName,
> O.OrderDate,
> E.LastName AS 'Registered By'
> FROM Customers C
> JOIN Orders O
> ON C.CustomerID=O.CustomerID
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> ORDER BY C.CompanyName
>
> I need all information of orders for particular company, appear under the
> element of that company but the query that I have written returns the name
> of company again and again for each order:
> SELECT
> C.CompanyName AS "@.Company",
> O.OrderDate AS "Order/OrderDate",
> E.LastName AS "Order/RegisteredBy"
> FROM Customers C
> JOIN Orders O
> ON C.CustomerID=O.CustomerID
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> ORDER BY C.CompanyName
> FOR XML PATH
> Any help would be greatly appreciated.
> Leila
>
>|||Good documentation can help ;)
You can make your query slightly easier to maintain by writing the subquery
as
(SELECT
O.OrderDate AS 'OrderDate',
E.LastName AS 'RegisteredBy'
FROM Orders O
JOIN Employees E
ON E.EmployeeID=O.EmployeeID
WHERE O.CustomerID=C.CUstomerID
FOR XML PATH('Order'),type)
Best regards
Michael
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23LnD5B3IGHA.3696@.TK2MSFTNGP15.phx.gbl...
> oops! seems that the problem solved:
> SELECT
> C.CompanyName AS "@.Company",
> (SELECT
> O.OrderDate AS 'Order/OrderDate',
> E.LastName AS 'Order/RegisteredBy'
> FROM Orders O
> JOIN Employees E
> ON E.EmployeeID=O.EmployeeID
> WHERE O.CustomerID=C.CUstomerID
> FOR XML PATH(''),type)
> FROM Customers C
> ORDER BY C.CompanyName
> FOR XML PATH('Customers'),ROOT('ROOT')
> Thanks to BOL documentation team :-)
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23AjU8w2IGHA.1676@.TK2MSFTNGP09.phx.gbl...
>

FOR XML not working in a subquery

The following seems to work in SQL Server 2005, but I'm getting the
error 'Incorrect syntax near xml' when I run it in SQL Server 2000.

select a.accountid , (select street, city, state, zip from account b
where a.accountid =b.accountid for xml auto, elements) as xmldata
from account a

The idea is to return 2 columns:
accountid
xmldata (address as xml)

Assuming the fields are correct, any ideas on what the problem might be?(jonathaneggert@.hotmail.com) writes:

Quote:

Originally Posted by

The following seems to work in SQL Server 2005, but I'm getting the
error 'Incorrect syntax near xml' when I run it in SQL Server 2000.
>
>
select a.accountid , (select street, city, state, zip from account b
where a.accountid =b.accountid for xml auto, elements) as xmldata
from account a
>
The idea is to return 2 columns:
accountid
xmldata (address as xml)
>
Assuming the fields are correct, any ideas on what the problem might be?

The problem is simply that you try to achieve something which is not
possible in SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Well thanks for such a detailed explanation of the answer.

Why can't this be done in SQL Server 2000? It is a sub-query which I
have used extensively in SQL Server 2000--why the problem with FOR XML?

Erland Sommarskog wrote:

Quote:

Originally Posted by

(jonathaneggert@.hotmail.com) writes:

Quote:

Originally Posted by

The following seems to work in SQL Server 2005, but I'm getting the
error 'Incorrect syntax near xml' when I run it in SQL Server 2000.

select a.accountid , (select street, city, state, zip from account b
where a.accountid =b.accountid for xml auto, elements) as xmldata
from account a

The idea is to return 2 columns:
accountid
xmldata (address as xml)

Assuming the fields are correct, any ideas on what the problem might be?

>
The problem is simply that you try to achieve something which is not
possible in SQL 2000.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||(jonathaneggert@.hotmail.com) writes:

Quote:

Originally Posted by

Well thanks for such a detailed explanation of the answer.
>
Why can't this be done in SQL Server 2000? It is a sub-query which I
have used extensively in SQL Server 2000--why the problem with FOR XML?

In SQL 2000, FOR XML can only be used in the outermost SELECT, to
produce a one-row, one-column result set. It cannot be used in subqueries,
derived tables. A good reason for this is that in SQL 2000, there is
not really any xml data type. Yet the result set returned by a FOR
XML clause is not really any of the SQL Server data types - it's XML.
It works thanks to some special hooks in the client APIs that can see
that here comes a one-row, one-column result set, which is an XML
document. There is no plumbing to permit FOR XML be composed with other
sorts of data.

This is all different in SQL 2005, where XML is a first-class citizen.

See also Books Online, the topic
XML and Internet Support ->
Retrieving and Writing XML Data ->
Retrieving XML Documents Using FOR XML ->
Guidelines for Using the FOR XML Clause

this topic lists a number of limitations with FOR XML.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

FOR XML EXPLICIT Question

I am trying to bcp data from the Customers table in the Northwind database
using the following format:
<Customer>
<CustomerID>ALFKI</CustomerID>
<Company>Alfreds Futterkiste</Company>
</Customer>
<Contact>
<Name>Maria Anders</Name>
<Title>Sales Representative</Title>
<Contact>
The query will always return one record at a time, but needs to be split
with the Customer tag and the Contact tag. My query is the following:
select 1 as Tag, NULL as Parent,
CustomerID AS [Customer!1!CustomerID!element],
CompanyName AS [Customer!1!Company!element],
ContactName AS [Contact!2!Name!element],
ContactTitle AS [Contact!2!Title!element]
FROM Customers WHERE PostalCode = '12209'
UNION ALL
select 2 as Tag, NULL as Parent,
CustomerID AS [Customer!1!CustomerID!element],
CompanyName AS [Customer!1!Company!element],
ContactName AS [Contact!2!Name!element],
ContactTitle AS [Contact!2!Title!element]
FROM Customers WHERE PostalCode = '12209'
FOR XML EXPLICIT
If I alter my select statement to just run the first select and use bcp to
export it, I can view the results in the XML format. But when I run the
complete statement and use bcp, when I view the document, the format appears
in non-XML format (basically, have to edit with Notpad, as opposed to
viewing it with Internet Explorer). Is there something wrong with my
statement?
TIA
Darin H.
The XML document format requires that you have a single top-level element
node. Your example data (and your query) generates what is commonly called a
fragment (ie more than one top-level element).
You can either use the root property of the SQLOLEDB provider (or ADO), use
a SQLXML template (again via the providers) or use another selection in our
FOR XML EXPLICIT query to add a root around the result.
Feel free to check out some of the FOR XML postings on my blog at
http://sqljunkies.com/weblog/mrys (or the FOR XML whitepaper link also
provided there) for seeing such an EXPLICIT mode query explained (and how
SQL Server 2005 will make it simpler).
Regarding your query below: probably should add an "order by
[Customer!1!CustomerID!element]" and you can leave the Contactname and title
in the first and the CompanyName in the second selection as NULL.
HTH
Michael
"Darin Hobbs" <darinh@.callow.ca> wrote in message
news:%237W$Kh6jEHA.1904@.TK2MSFTNGP09.phx.gbl...
>I am trying to bcp data from the Customers table in the Northwind database
> using the following format:
> <Customer>
> <CustomerID>ALFKI</CustomerID>
> <Company>Alfreds Futterkiste</Company>
> </Customer>
> <Contact>
> <Name>Maria Anders</Name>
> <Title>Sales Representative</Title>
> <Contact>
> The query will always return one record at a time, but needs to be split
> with the Customer tag and the Contact tag. My query is the following:
> select 1 as Tag, NULL as Parent,
> CustomerID AS [Customer!1!CustomerID!element],
> CompanyName AS [Customer!1!Company!element],
> ContactName AS [Contact!2!Name!element],
> ContactTitle AS [Contact!2!Title!element]
> FROM Customers WHERE PostalCode = '12209'
> UNION ALL
> select 2 as Tag, NULL as Parent,
> CustomerID AS [Customer!1!CustomerID!element],
> CompanyName AS [Customer!1!Company!element],
> ContactName AS [Contact!2!Name!element],
> ContactTitle AS [Contact!2!Title!element]
> FROM Customers WHERE PostalCode = '12209'
> FOR XML EXPLICIT
> If I alter my select statement to just run the first select and use bcp to
> export it, I can view the results in the XML format. But when I run the
> complete statement and use bcp, when I view the document, the format
> appears
> in non-XML format (basically, have to edit with Notpad, as opposed to
> viewing it with Internet Explorer). Is there something wrong with my
> statement?
> TIA
> Darin H.
>

FOR XML EXPLICIT Over Multiple Stored Procedures

Hi,
I was wondering if the following was possible in VB/SQL?
To call one stored procedure, eg. sp_Referrals, which in turn calls
two stored procedures that each return their own XML structure. then
to combine these two results sets, before returning them as one
record/output/stream query result?
For example,
sp_CustomerSummary calls
exec sp_GetReferrals @.CustomerId
exec sp_GetReferenceData 'Titles'
which return the following XML respectively:
<Referrals ID="1"><Reason>gfgdfgdfg</Reason><CreationDate>Apr 27 2004
2:01PM</CreationDate><PVDetails>gdfgfdgdfgdf</PVDetails><BenefitType>RP</BenefitType><LSCAction>Yes</LSCAction><PVCategory>Member
of Household</PVCategory></Referrals>
AND
<Reference RefType="Titles"><Option Value="8" Text=""/><Option
Value="7" Text="Dr"/><Option Value="9" Text="Miss"/><Option Value="5"
Text="Mr"/><Option Value="6" Text="Mrs"/></Reference>
they have different structures, obviously, but I would now like to
combine these and return them as one XML as children of a single
<RootNode> and as one recordset.
Is this possible please?
Thanks in advance
Jane
I don't think this is easily possible in SQL Server 2000. But you can do it
in SQL Server 2005 (formerly known as Yukon), out to be in beta in summer.
Note that you will not be able to use stored procs (since they use
side-effects to return data), but you will be able to use user defined
functions...
Best regards
Michael
"Jane" <fitzfreckle@.yahoo.co.uk> wrote in message
news:b709383b.0404280323.46b933be@.posting.google.c om...
> Hi,
> I was wondering if the following was possible in VB/SQL?
> To call one stored procedure, eg. sp_Referrals, which in turn calls
> two stored procedures that each return their own XML structure. then
> to combine these two results sets, before returning them as one
> record/output/stream query result?
> For example,
> sp_CustomerSummary calls
> exec sp_GetReferrals @.CustomerId
> exec sp_GetReferenceData 'Titles'
> which return the following XML respectively:
> <Referrals ID="1"><Reason>gfgdfgdfg</Reason><CreationDate>Apr 27 2004
> 2:01PM</CreationDate><PVDetails>gdfgfdgdfgdf</PVDetails><BenefitType>RP</BenefitType><LSCAction>Yes</LSCAction><PVCategory>Member
> of Household</PVCategory></Referrals>
>
> AND
> <Reference RefType="Titles"><Option Value="8" Text=""/><Option
> Value="7" Text="Dr"/><Option Value="9" Text="Miss"/><Option Value="5"
> Text="Mr"/><Option Value="6" Text="Mrs"/></Reference>
> they have different structures, obviously, but I would now like to
> combine these and return them as one XML as children of a single
> <RootNode> and as one recordset.
> Is this possible please?
> Thanks in advance
> Jane

FOR XML EXPLICIT issue

Hi guys:
I'm in an urgent need to know if we can dynamically alter values in
querying tables using FOR XML EXPLICIT. In the following sample, I have
3 tabels involved vis_Rule, vis_IF, and vis_AND.
Table Structure:
Table: vis_Rule
Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
Table: vis_IF
Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
Table: vis_AND
Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++
In the following stored proc, can we grab the RuleId of the first
SELECT statement and pass it to the next SELECT statement such that it
extracts only the desired rows? In other words, can we dynamically
assign values from one part of the SELECT statement to the next?
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id],
NULL as [compare!4!id],
NULL as [compare!4!operator]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL,
NULL,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
NULL,
if.IFId,
[and].AndId,
NULL,
NULL
FROM vis_AND [and], vis_IF if
where [and].IFId = if.IFId
FOR XML EXPLICIT
GO
Thanks in advance.My apologies for the late reply, but I was on vacation the last couple of
ws.
I am not quite sure what you try to acheive. Do you want to only provide the
nesting of the tree for a given ruleID?
In that case try:
CREATE Table vis_Rule (RuleId int,Name nvarchar(40), Priority int, Active
bit)
go
insert into vis_Rule values (1, N'r1', 1, 1)
insert into vis_Rule values (2, N'r2', 2, 1)
go
CREATE Table vis_IF (IFId int, RuleId int --> Foreign Key to RuleId in
vis_Rule
)
go
insert into vis_IF values (1, 1)
insert into vis_IF values (2, 1)
insert into vis_IF values (3, 1)
insert into vis_IF values (4, 2)
go
CREATE Table vis_AND (AndId int, IFId int --> Foreign Key to IFId in vis_IF
)
insert into vis_AND values (1, 1)
insert into vis_AND values (2, 1)
insert into vis_AND values (3, 2)
insert into vis_AND values (4, 2)
insert into vis_AND values (5, 3)
insert into vis_AND values (6, 4)
go
declare @.rid int
set @.rid = 1
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
where r.RuleId = @.rid
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = @.rid
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
[if].RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if]
where [and].IFId = [if].IFId
AND [if].RuleId = @.rid
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
If you want it for all rules, try:
SELECT 1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = [if].RuleId
AND [and].IFId = [if].IFId
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
Best regards
Michael
<shamod@.gmail.com> wrote in message
news:1122527565.227837.240190@.g43g2000cwa.googlegroups.com...
> Hi guys:
> I'm in an urgent need to know if we can dynamically alter values in
> querying tables using FOR XML EXPLICIT. In the following sample, I have
> 3 tabels involved vis_Rule, vis_IF, and vis_AND.
> Table Structure:
> Table: vis_Rule
> Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
> Table: vis_IF
> Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
> Table: vis_AND
> Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
> ++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++
> In the following stored proc, can we grab the RuleId of the first
> SELECT statement and pass it to the next SELECT statement such that it
> extracts only the desired rows? In other words, can we dynamically
> assign values from one part of the SELECT statement to the next?
> SELECT TOP 1
> 1 AS Tag,
> NULL AS Parent,
> r.RuleId as [rule!1!Id],
> r.Name as [rule!1!name],
> r.Priority as [rule!1!priority],
> r.Active as [rule!1!active],
> NULL as [if!2!id],
> NULL as [and!3!id],
> NULL as [compare!4!id],
> NULL as [compare!4!operator]
> FROM vis_Rule r
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> r.RuleId,
> NULL,
> NULL,
> NULL,
> [if].IFId,
> NULL,
> NULL,
> NULL
> FROM vis_IF [if], vis_Rule r
> WHERE [if].RuleId = r.RuleId
> UNION ALL
> SELECT 3 AS Tag,
> 2 AS Parent,
> NULL,
> NULL,
> NULL,
> NULL,
> if.IFId,
> [and].AndId,
> NULL,
> NULL
> FROM vis_AND [and], vis_IF if
> where [and].IFId = if.IFId
> FOR XML EXPLICIT
> GO
> Thanks in advance.
>

FOR XML EXPLICIT issue

Hi guys:
I'm in an urgent need to know if we can dynamically alter values in
querying tables using FOR XML EXPLICIT. In the following sample, I have
3 tabels involved vis_Rule, vis_IF, and vis_AND.
Table Structure:
Table: vis_Rule
Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
Table: vis_IF
Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
Table: vis_AND
Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++
In the following stored proc, can we grab the RuleId of the first
SELECT statement and pass it to the next SELECT statement such that it
extracts only the desired rows? In other words, can we dynamically
assign values from one part of the SELECT statement to the next?
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id],
NULL as [compare!4!id],
NULL as [compare!4!operator]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL,
NULL,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
NULL,
if.IFId,
[and].AndId,
NULL,
NULL
FROM vis_AND [and], vis_IF if
where [and].IFId = if.IFId
FOR XML EXPLICIT
GO
Thanks in advance.
My apologies for the late reply, but I was on vacation the last couple of
weeks.
I am not quite sure what you try to acheive. Do you want to only provide the
nesting of the tree for a given ruleID?
In that case try:
CREATE Table vis_Rule (RuleId int,Name nvarchar(40), Priority int, Active
bit)
go
insert into vis_Rule values (1, N'r1', 1, 1)
insert into vis_Rule values (2, N'r2', 2, 1)
go
CREATE Table vis_IF (IFId int, RuleId int --> Foreign Key to RuleId in
vis_Rule
)
go
insert into vis_IF values (1, 1)
insert into vis_IF values (2, 1)
insert into vis_IF values (3, 1)
insert into vis_IF values (4, 2)
go
CREATE Table vis_AND (AndId int, IFId int --> Foreign Key to IFId in vis_IF
)
insert into vis_AND values (1, 1)
insert into vis_AND values (2, 1)
insert into vis_AND values (3, 2)
insert into vis_AND values (4, 2)
insert into vis_AND values (5, 3)
insert into vis_AND values (6, 4)
go
declare @.rid int
set @.rid = 1
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
where r.RuleId = @.rid
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = @.rid
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
[if].RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if]
where [and].IFId = [if].IFId
AND [if].RuleId = @.rid
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
If you want it for all rules, try:
SELECT 1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = [if].RuleId
AND [and].IFId = [if].IFId
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
Best regards
Michael
<shamod@.gmail.com> wrote in message
news:1122527565.227837.240190@.g43g2000cwa.googlegr oups.com...
> Hi guys:
> I'm in an urgent need to know if we can dynamically alter values in
> querying tables using FOR XML EXPLICIT. In the following sample, I have
> 3 tabels involved vis_Rule, vis_IF, and vis_AND.
> Table Structure:
> Table: vis_Rule
> Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
> Table: vis_IF
> Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
> Table: vis_AND
> Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
> ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++
> In the following stored proc, can we grab the RuleId of the first
> SELECT statement and pass it to the next SELECT statement such that it
> extracts only the desired rows? In other words, can we dynamically
> assign values from one part of the SELECT statement to the next?
> SELECT TOP 1
> 1 AS Tag,
> NULL AS Parent,
> r.RuleId as [rule!1!Id],
> r.Name as [rule!1!name],
> r.Priority as [rule!1!priority],
> r.Active as [rule!1!active],
> NULL as [if!2!id],
> NULL as [and!3!id],
> NULL as [compare!4!id],
> NULL as [compare!4!operator]
> FROM vis_Rule r
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> r.RuleId,
> NULL,
> NULL,
> NULL,
> [if].IFId,
> NULL,
> NULL,
> NULL
> FROM vis_IF [if], vis_Rule r
> WHERE [if].RuleId = r.RuleId
> UNION ALL
> SELECT 3 AS Tag,
> 2 AS Parent,
> NULL,
> NULL,
> NULL,
> NULL,
> if.IFId,
> [and].AndId,
> NULL,
> NULL
> FROM vis_AND [and], vis_IF if
> where [and].IFId = if.IFId
> FOR XML EXPLICIT
> GO
> Thanks in advance.
>

for xml explicit data problem

Hi
I am running the following stored proc by executing a data set in c#.
surprisigly I am getting data padded with extra characters.
something like this - \"christy john"\. How to get rid of '\' this
character.
i try search and replace with regexp as well as string replace
functions without luck.
Here is the actual xml look like when I read from data set in c#.
<TREENODES><treenode text=\"A/L ALLANDOO, FELIX\"/><treenode
text=\"BEDI, RAHUL\"/><treenode text=\"CHALMERS, WILLIAM\"><treenode
text=\"CNOSSEN, RICK\"><treenode text=\"ANKALA, PRAVEEN\"/><treenode
text=\"BOGIA, DOUGLAS\"/><treenode text=\"COOK, HAROLD\"/><treenode
text=\"DAILY, STEPHEN\"/><treenode text=\"GAN, XIAOCHUN\"/><treenode
text=\"GARCEAU, PAUL\"/><treenode text=\"GOTTHARDT,
STEVEN\"/><treenode text=\"GUPTA, MEENAKSHI\"/></TREENODES>
Query analyser data in SQL SERVER looks perfectly alright without
special
character padding like shown beow.
<TREENODES><treenode text="A/L ALLANDOO, FELIX"/><treenode text="BEDI,
RAHUL"/><treenode text="CHALMERS, WILLIAM"><treenode text="CNOSSEN,
RICK"><treenode text="ANKALA, PRAVEEN"/><treenode text="BOGIA,
DOUGLAS"/><treenode text="COOK, HAROLD"/><treenode tex
Please help me.
Rao
-- Here is c# code --
ds = SqlHelper.ExecuteDataset(ConfigurationSettings.App Settings[ASPNET.StarterKit.TimeTracker.Web.Global. CfgKeyConnString],
SPname);
System.IO.StringWriter strWriter = new StringWriter();
ds.WriteXml(strWriter);
strXML = strWriter.ToString();
--Here is stored procedure and c# code--
dbo.ResourceXMLTest
as
set nocount on
--Take out the next block and make this SP accept
--a variable table like it does for @.EmpToMan
SET CONCAT_NULL_YIELDS_NULL OFF
Declare @.EmpToMan table(
ResourceID int,
ManagerID int,
Depth int,
Lineage nvarchar(256),
ResourceWWID numeric(19),
RFirstName nvarchar(50),
RLastName nvarchar(50)
)
INSERT INTO @.EmpToMan
SELECT
E.EmployeeID,
E.ManagerID,
E.Depth,
E.Lineage,
R.WWID,
R.FirstName ,
R.LastName + ', ' + R.FirstName
--R.LastName + ', ' + R.FirstName
FROM
Resources R left outer join EmployeeToManager E
ON r.resourceID = E.EmployeeID
select
1 as Tag,
0 as Parent,
null as [TREENODES!1!text],
null as [treenode!2!text],
null as [treenode!3!text],
null as [treenode!4!text],
null as [treenode!5!text],
null as [treenode!6!text],
null as [treenode!7!text],
null as [treenode!8!text],
null as [treenode!9!text],
null as [treenode!10!text],
null as [treenode!11!text],
null as [treenode!12!text],
null as [treenode!13!text]
union all
select
2 as Tag,
1 as Parent,
null,
RLastName as [treenode!2!text],
null as [treenode!3!text],
null as [treenode!4!text],
null as [treenode!5!text],
null as [treenode!6!text],
null as [treenode!7!text],
null as [treenode!8!text],
null as [treenode!9!text],
null as [treenode!10!text],
null as [treenode!11!text],
null as [treenode!12!text],
null as [treenode!13!text]
from
@.EmpToMan
where
Depth + 1 = 1
union
all
select
3 as Tag,
2 as Parent,
null,
t1.RLastName,
t2.RLastName as [treenode!3!text],
null as [treenode!4!text],
null as [treenode!5!text],
null as [treenode!6!text],
null as [treenode!7!text],
null as [treenode!8!text],
null as [treenode!9!text],
null as [treenode!10!text],
null as [treenode!11!text],
null as [treenode!12!text],
null as [treenode!13!text]
from
@.EmpToMan as t1
inner join
@.EmpToMan as t2
on
t1.ResourceID = t2.ManagerID
where
t1.depth + 1 = 1
and
t2.depth + 1 = 2
union
all
--Begin Block #4
select
4 as Tag,
3 as Parent,
null,
t1.RLastName,
t2.RLastName,
t3.RLastName as [treenode!4!text],
null as [treenode!5!text],
null as [treenode!6!text],
null as [treenode!7!text],
null as [treenode!8!text],
null as [treenode!9!text],
null as [treenode!10!text],
null as [treenode!11!text],
null as [treenode!12!text],
null as [treenode!13!text]
From
@.EmpToMan as t1
inner join
@.EmpToMan as t2
on
t1.ResourceID = t2.ManagerID
inner join
@.EmpToMan as t3
on
t2.ResourceID = t3.ManagerID
where
t1.depth +1 = 1
and
t2.depth + 1 = 2
and
t3.depth + 1 = 3
--End Block
union all
--Begin Block #5
select
5 as Tag,
4 as Parent,
null,
t1.RLastName,
t2.RLastName,
t3.RLastName as [treenode!4!text],
t4.RLastName as [treenode!5!text],
null as [treenode!6!text],
null as [treenode!7!text],
null as [treenode!8!text],
null as [treenode!9!text],
null as [treenode!10!text],
null as [treenode!11!text],
null as [treenode!12!text],
null as [treenode!13!text]
From @.EmpToMan as t1
inner join@.EmpToMan as t2
on t1.ResourceID = t2.ManagerID
inner join @.EmpToMan as t3
on t2.ResourceID = t3.ManagerID
inner join @.EmpToMan as t4
on t3.ResourceID = t4.ManagerID
where t1.depth +1 = 1
andt2.depth + 1 = 2
and t3.depth + 1 = 3
and t4.depth + 1 = 4
--End Block
union all
--Begin Block #6
select
6 as Tag,
5 as Parent,
null,
t1.RLastName,
t2.RLastName,
t3.RLastName as [treenode!4!text],
t4.RLastName as [treenode!5!text],
t5.RLastName as [treenode!6!text],
null as [treenode!7!text],
null as [treenode!8!text],
null as [treenode!9!text],
null as [treenode!10!text],
null as [treenode!11!text],
null as [treenode!12!text],
null as [treenode!13!text]
From @.EmpToMan as t1
inner join@.EmpToMan as t2
on t1.ResourceID = t2.ManagerID
inner join @.EmpToMan as t3
on t2.ResourceID = t3.ManagerID
inner join @.EmpToMan as t4
on t3.ResourceID = t4.ManagerID
inner join @.EmpToMan as t5
on t4.ResourceID = t5.ManagerID
where t1.depth +1 = 1
andt2.depth + 1 = 2
and t3.depth + 1 = 3
and t4.depth + 1 = 4
and t5.depth + 1 = 5
--End Block
union all
--Begin Block #7
select
7 as Tag,
6 as Parent,
null,
t1.RLastName,
t2.RLastName,
t3.RLastName as [treenode!4!text],
t4.RLastName as [treenode!5!text],
t5.RLastName as [treenode!6!text],
t6.RLastName as [treenode!7!text],
null as [treenode!8!text],
null as [treenode!9!text],
null as [treenode!10!text],
null as [treenode!11!text],
null as [treenode!12!text],
null as [treenode!13!text]
From @.EmpToMan as t1
inner join@.EmpToMan as t2
on t1.ResourceID = t2.ManagerID
inner join @.EmpToMan as t3
on t2.ResourceID = t3.ManagerID
inner join @.EmpToMan as t4
on t3.ResourceID = t4.ManagerID
inner join @.EmpToMan as t5
on t4.ResourceID = t5.ManagerID
inner join @.EmpToMan as t6
on t5.ResourceID = t6.ManagerID
where t1.depth +1 = 1
andt2.depth + 1 = 2
and t3.depth + 1 = 3
and t4.depth + 1 = 4
and t5.depth + 1 = 5
and t6.depth + 1 = 6
--End Block
union all
--Begin Block #8
select
8 as Tag,
7 as Parent,
null,
t1.RLastName,
t2.RLastName,
t3.RLastName as [treenode!4!text],
t4.RLastName as [treenode!5!text],
t5.RLastName as [treenode!6!text],
t6.RLastName as [treenode!7!text],
t7.RLastName as [treenode!8!text],
null as [treenode!9!text],
null as [treenode!10!text],
null as [treenode!11!text],
null as [treenode!12!text],
null as [treenode!13!text]
From @.EmpToMan as t1
inner join@.EmpToMan as t2
on t1.ResourceID = t2.ManagerID
inner join @.EmpToMan as t3
on t2.ResourceID = t3.ManagerID
inner join @.EmpToMan as t4
on t3.ResourceID = t4.ManagerID
inner join @.EmpToMan as t5
on t4.ResourceID = t5.ManagerID
inner join @.EmpToMan as t6
on t5.ResourceID = t6.ManagerID
inner join @.EmpToMan as t7
on t6.ResourceID = t7.ManagerID
where t1.depth +1 = 1
andt2.depth + 1 = 2
and t3.depth + 1 = 3
and t4.depth + 1 = 4
and t5.depth + 1 = 5
and t6.depth + 1 = 6
and t7.depth + 1 = 7
--End Block
union all
--Begin Block #9
select
9 as Tag,
8 as Parent,
null,
t1.RLastName,
t2.RLastName,
t3.RLastName as [treenode!4!text],
t4.RLastName as [treenode!5!text],
t5.RLastName as [treenode!6!text],
t6.RLastName as [treenode!7!text],
t7.RLastName as [treenode!8!text],
t8.RLastName as [treenode!9!text],
null as [treenode!10!text],
null as [treenode!11!text],
null as [treenode!12!text],
null as [treenode!13!text]
From @.EmpToMan as t1
inner join@.EmpToMan as t2
on t1.ResourceID = t2.ManagerID
inner join @.EmpToMan as t3
on t2.ResourceID = t3.ManagerID
inner join @.EmpToMan as t4
on t3.ResourceID = t4.ManagerID
inner join @.EmpToMan as t5
on t4.ResourceID = t5.ManagerID
inner join @.EmpToMan as t6
on t5.ResourceID = t6.ManagerID
inner join @.EmpToMan as t7
on t6.ResourceID = t7.ManagerID
inner join @.EmpToMan as t8
on t7.ResourceID = t8.ManagerID
where t1.depth +1 = 1
andt2.depth + 1 = 2
and t3.depth + 1 = 3
and t4.depth + 1 = 4
and t5.depth + 1 = 5
and t6.depth + 1 = 6
and t7.depth + 1 = 7
and t8.depth + 1 = 8
--End Block
union all
--Begin Block #10
select
10 as Tag,
9 as Parent,
null,
t1.RLastName,
t2.RLastName,
t3.RLastName as [treenode!4!text],
t4.RLastName as [treenode!5!text],
t5.RLastName as [treenode!6!text],
t6.RLastName as [treenode!7!text],
t7.RLastName as [treenode!8!text],
t8.RLastName as [treenode!9!text],
t9.RLastName as [treenode!10!text],
null as [treenode!11!text],
null as [treenode!12!text],
null as [treenode!13!text]
From @.EmpToMan as t1
inner join@.EmpToMan as t2
on t1.ResourceID = t2.ManagerID
inner join @.EmpToMan as t3
on t2.ResourceID = t3.ManagerID
inner join @.EmpToMan as t4
on t3.ResourceID = t4.ManagerID
inner join @.EmpToMan as t5
on t4.ResourceID = t5.ManagerID
inner join @.EmpToMan as t6
on t5.ResourceID = t6.ManagerID
inner join @.EmpToMan as t7
on t6.ResourceID = t7.ManagerID
inner join @.EmpToMan as t8
on t7.ResourceID = t8.ManagerID
inner join @.EmpToMan as t9
on t8.ResourceID = t9.ManagerID
where t1.depth +1 = 1
andt2.depth + 1 = 2
and t3.depth + 1 = 3
and t4.depth + 1 = 4
and t5.depth + 1 = 5
and t6.depth + 1 = 6
and t7.depth + 1 = 7
and t8.depth + 1 = 8
and t9.depth + 1 = 9
--End Block
union all
--Begin Block #11
select
11 as Tag,
10 as Parent,
null,
t1.RLastName,
t2.RLastName,
t3.RLastName as [treenode!4!text],
t4.RLastName as [treenode!5!text],
t5.RLastName as [treenode!6!text],
t6.RLastName as [treenode!7!text],
t7.RLastName as [treenode!8!text],
t8.RLastName as [treenode!9!text],
t9.RLastName as [treenode!10!text],
t10.RLastName as [treenode!11!text],
null as [treenode!12!text],
null as [treenode!13!text]
From @.EmpToMan as t1
inner join@.EmpToMan as t2
on t1.ResourceID = t2.ManagerID
inner join @.EmpToMan as t3
on t2.ResourceID = t3.ManagerID
inner join @.EmpToMan as t4
on t3.ResourceID = t4.ManagerID
inner join @.EmpToMan as t5
on t4.ResourceID = t5.ManagerID
inner join @.EmpToMan as t6
on t5.ResourceID = t6.ManagerID
inner join @.EmpToMan as t7
on t6.ResourceID = t7.ManagerID
inner join @.EmpToMan as t8
on t7.ResourceID = t8.ManagerID
inner join @.EmpToMan as t9
on t8.ResourceID = t9.ManagerID
inner join @.EmpToMan as t10
on t9.ResourceID = t10.ManagerID
where t1.depth +1 = 1
andt2.depth + 1 = 2
and t3.depth + 1 = 3
and t4.depth + 1 = 4
and t5.depth + 1 = 5
and t6.depth + 1 = 6
and t7.depth + 1 = 7
and t8.depth + 1 = 8
and t9.depth + 1 = 9
and t10.depth + 1 = 10
--End Block
union all
--Begin Block #12
select
12 as Tag,
11 as Parent,
null,
t1.RLastName,
t2.RLastName,
t3.RLastName as [treenode!4!text],
t4.RLastName as [treenode!5!text],
t5.RLastName as [treenode!6!text],
t6.RLastName as [treenode!7!text],
t7.RLastName as [treenode!8!text],
t8.RLastName as [treenode!9!text],
t9.RLastName as [treenode!10!text],
t10.RLastName as [treenode!11!text],
t11.RLastName as [treenode!12!text],
null as [treenode!13!text]
From @.EmpToMan as t1
inner join@.EmpToMan as t2
on t1.ResourceID = t2.ManagerID
inner join @.EmpToMan as t3
on t2.ResourceID = t3.ManagerID
inner join @.EmpToMan as t4
on t3.ResourceID = t4.ManagerID
inner join @.EmpToMan as t5
on t4.ResourceID = t5.ManagerID
inner join @.EmpToMan as t6
on t5.ResourceID = t6.ManagerID
inner join @.EmpToMan as t7
on t6.ResourceID = t7.ManagerID
inner join @.EmpToMan as t8
on t7.ResourceID = t8.ManagerID
inner join @.EmpToMan as t9
on t8.ResourceID = t9.ManagerID
inner join @.EmpToMan as t10
on t9.ResourceID = t10.ManagerID
inner join @.EmpToMan as t11
on t10.ResourceID = t11.ManagerID
where t1.depth +1 = 1
andt2.depth + 1 = 2
and t3.depth + 1 = 3
and t4.depth + 1 = 4
and t5.depth + 1 = 5
and t6.depth + 1 = 6
and t7.depth + 1 = 7
and t8.depth + 1 = 8
and t9.depth + 1 = 9
and t10.depth + 1 = 10
and t11.depth + 1 = 11
--End Block
union all
--Begin Block #13
select
13 as Tag,
12 as Parent,
null,
t1.RLastName,
t2.RLastName,
t3.RLastName as [treenode!4!text],
t4.RLastName as [treenode!5!text],
t5.RLastName as [treenode!6!text],
t6.RLastName as [treenode!7!text],
t7.RLastName as [treenode!8!text],
t8.RLastName as [treenode!9!text],
t9.RLastName as [treenode!10!text],
t10.RLastName as [treenode!11!text],
t11.RLastName as [treenode!12!text],
t12.RLastName as [treenode!13!text]
From @.EmpToMan as t1
inner join@.EmpToMan as t2
on t1.ResourceID = t2.ManagerID
inner join @.EmpToMan as t3
on t2.ResourceID = t3.ManagerID
inner join @.EmpToMan as t4
on t3.ResourceID = t4.ManagerID
inner join @.EmpToMan as t5
on t4.ResourceID = t5.ManagerID
inner join @.EmpToMan as t6
on t5.ResourceID = t6.ManagerID
inner join @.EmpToMan as t7
on t6.ResourceID = t7.ManagerID
inner join @.EmpToMan as t8
on t7.ResourceID = t8.ManagerID
inner join @.EmpToMan as t9
on t8.ResourceID = t9.ManagerID
inner join @.EmpToMan as t10
on t9.ResourceID = t10.ManagerID
inner join @.EmpToMan as t11
on t10.ResourceID = t11.ManagerID
inner join @.EmpToMan as t12
on t11.ResourceID = t12.ManagerID
where t1.depth +1 = 1
andt2.depth + 1 = 2
and t3.depth + 1 = 3
and t4.depth + 1 = 4
and t5.depth + 1 = 5
and t6.depth + 1 = 6
and t7.depth + 1 = 7
and t8.depth + 1 = 8
and t9.depth + 1 = 9
and t10.depth + 1 = 10
and t11.depth + 1 = 11
and t12.depth + 1 = 12
--End Block
order by
[TREENODES!1!text],
[treenode!2!text],
[treenode!3!text],
[treenode!4!text],
[treenode!5!text],
[treenode!6!text],
[treenode!7!text],
[treenode!8!text],
[treenode!9!text],
[treenode!10!text],
[treenode!11!text],
[treenode!12!text],
[treenode!13!text]
for xml explicit
This looks like a C# issue. Are you sure that the \ is actually part of the
data? It could be that you see \" as a way to escape the " for some C#
specific reason and that the data still only contains ". You may want to
check in a C# specific newsgroup if that does not explain it.
HTH
Michael
"rao" <jayamallik@.hotmail.com> wrote in message
news:f2ff1cc.0408251644.2bf22b62@.posting.google.co m...
> Hi
> I am running the following stored proc by executing a data set in c#.
> surprisigly I am getting data padded with extra characters.
> something like this - \"christy john"\. How to get rid of '\' this
> character.
> i try search and replace with regexp as well as string replace
> functions without luck.
>
> Here is the actual xml look like when I read from data set in c#.
> <TREENODES><treenode text=\"A/L ALLANDOO, FELIX\"/><treenode
> text=\"BEDI, RAHUL\"/><treenode text=\"CHALMERS, WILLIAM\"><treenode
> text=\"CNOSSEN, RICK\"><treenode text=\"ANKALA, PRAVEEN\"/><treenode
> text=\"BOGIA, DOUGLAS\"/><treenode text=\"COOK, HAROLD\"/><treenode
> text=\"DAILY, STEPHEN\"/><treenode text=\"GAN, XIAOCHUN\"/><treenode
> text=\"GARCEAU, PAUL\"/><treenode text=\"GOTTHARDT,
> STEVEN\"/><treenode text=\"GUPTA, MEENAKSHI\"/></TREENODES>
>
> Query analyser data in SQL SERVER looks perfectly alright without
> special
> character padding like shown beow.
> <TREENODES><treenode text="A/L ALLANDOO, FELIX"/><treenode text="BEDI,
> RAHUL"/><treenode text="CHALMERS, WILLIAM"><treenode text="CNOSSEN,
> RICK"><treenode text="ANKALA, PRAVEEN"/><treenode text="BOGIA,
> DOUGLAS"/><treenode text="COOK, HAROLD"/><treenode tex
> Please help me.
> Rao
>
> -- Here is c# code --
> ds =
> SqlHelper.ExecuteDataset(ConfigurationSettings.App Settings[ASPNET.StarterKit.TimeTracker.Web.Global. CfgKeyConnString],
> SPname);
> System.IO.StringWriter strWriter = new StringWriter();
> ds.WriteXml(strWriter);
> strXML = strWriter.ToString();
> --Here is stored procedure and c# code--
> dbo.ResourceXMLTest
> as
> set nocount on
> --Take out the next block and make this SP accept
> --a variable table like it does for @.EmpToMan
> SET CONCAT_NULL_YIELDS_NULL OFF
> Declare @.EmpToMan table(
> ResourceID int,
> ManagerID int,
> Depth int,
> Lineage nvarchar(256),
> ResourceWWID numeric(19),
> RFirstName nvarchar(50),
> RLastName nvarchar(50)
> )
> INSERT INTO @.EmpToMan
> SELECT
> E.EmployeeID,
> E.ManagerID,
> E.Depth,
> E.Lineage,
> R.WWID,
> R.FirstName ,
> R.LastName + ', ' + R.FirstName
> --R.LastName + ', ' + R.FirstName
> FROM
> Resources R left outer join EmployeeToManager E
> ON r.resourceID = E.EmployeeID
> select
> 1 as Tag,
> 0 as Parent,
> null as [TREENODES!1!text],
> null as [treenode!2!text],
> null as [treenode!3!text],
> null as [treenode!4!text],
> null as [treenode!5!text],
> null as [treenode!6!text],
> null as [treenode!7!text],
> null as [treenode!8!text],
> null as [treenode!9!text],
> null as [treenode!10!text],
> null as [treenode!11!text],
> null as [treenode!12!text],
> null as [treenode!13!text]
> union all
> select
> 2 as Tag,
> 1 as Parent,
> null,
> RLastName as [treenode!2!text],
> null as [treenode!3!text],
> null as [treenode!4!text],
> null as [treenode!5!text],
> null as [treenode!6!text],
> null as [treenode!7!text],
> null as [treenode!8!text],
> null as [treenode!9!text],
> null as [treenode!10!text],
> null as [treenode!11!text],
> null as [treenode!12!text],
> null as [treenode!13!text]
> from
> @.EmpToMan
> where
> Depth + 1 = 1
> union
> all
> select
> 3 as Tag,
> 2 as Parent,
> null,
> t1.RLastName,
> t2.RLastName as [treenode!3!text],
> null as [treenode!4!text],
> null as [treenode!5!text],
> null as [treenode!6!text],
> null as [treenode!7!text],
> null as [treenode!8!text],
> null as [treenode!9!text],
> null as [treenode!10!text],
> null as [treenode!11!text],
> null as [treenode!12!text],
> null as [treenode!13!text]
> from
> @.EmpToMan as t1
> inner join
> @.EmpToMan as t2
> on
> t1.ResourceID = t2.ManagerID
> where
> t1.depth + 1 = 1
> and
> t2.depth + 1 = 2
> union
> all
> --Begin Block #4
> select
> 4 as Tag,
> 3 as Parent,
> null,
> t1.RLastName,
> t2.RLastName,
> t3.RLastName as [treenode!4!text],
> null as [treenode!5!text],
> null as [treenode!6!text],
> null as [treenode!7!text],
> null as [treenode!8!text],
> null as [treenode!9!text],
> null as [treenode!10!text],
> null as [treenode!11!text],
> null as [treenode!12!text],
> null as [treenode!13!text]
> From
> @.EmpToMan as t1
> inner join
> @.EmpToMan as t2
> on
> t1.ResourceID = t2.ManagerID
> inner join
> @.EmpToMan as t3
> on
> t2.ResourceID = t3.ManagerID
> where
> t1.depth +1 = 1
> and
> t2.depth + 1 = 2
> and
> t3.depth + 1 = 3
> --End Block
> union all
> --Begin Block #5
> select
> 5 as Tag,
> 4 as Parent,
> null,
> t1.RLastName,
> t2.RLastName,
> t3.RLastName as [treenode!4!text],
> t4.RLastName as [treenode!5!text],
> null as [treenode!6!text],
> null as [treenode!7!text],
> null as [treenode!8!text],
> null as [treenode!9!text],
> null as [treenode!10!text],
> null as [treenode!11!text],
> null as [treenode!12!text],
> null as [treenode!13!text]
> From @.EmpToMan as t1
> inner join @.EmpToMan as t2
> on t1.ResourceID = t2.ManagerID
> inner join @.EmpToMan as t3
> on t2.ResourceID = t3.ManagerID
> inner join @.EmpToMan as t4
> on t3.ResourceID = t4.ManagerID
> where t1.depth +1 = 1
> and t2.depth + 1 = 2
> and t3.depth + 1 = 3
> and t4.depth + 1 = 4
> --End Block
> union all
> --Begin Block #6
> select
> 6 as Tag,
> 5 as Parent,
> null,
> t1.RLastName,
> t2.RLastName,
> t3.RLastName as [treenode!4!text],
> t4.RLastName as [treenode!5!text],
> t5.RLastName as [treenode!6!text],
> null as [treenode!7!text],
> null as [treenode!8!text],
> null as [treenode!9!text],
> null as [treenode!10!text],
> null as [treenode!11!text],
> null as [treenode!12!text],
> null as [treenode!13!text]
> From @.EmpToMan as t1
> inner join @.EmpToMan as t2
> on t1.ResourceID = t2.ManagerID
> inner join @.EmpToMan as t3
> on t2.ResourceID = t3.ManagerID
> inner join @.EmpToMan as t4
> on t3.ResourceID = t4.ManagerID
> inner join @.EmpToMan as t5
> on t4.ResourceID = t5.ManagerID
> where t1.depth +1 = 1
> and t2.depth + 1 = 2
> and t3.depth + 1 = 3
> and t4.depth + 1 = 4
> and t5.depth + 1 = 5
> --End Block
> union all
> --Begin Block #7
> select
> 7 as Tag,
> 6 as Parent,
> null,
> t1.RLastName,
> t2.RLastName,
> t3.RLastName as [treenode!4!text],
> t4.RLastName as [treenode!5!text],
> t5.RLastName as [treenode!6!text],
> t6.RLastName as [treenode!7!text],
> null as [treenode!8!text],
> null as [treenode!9!text],
> null as [treenode!10!text],
> null as [treenode!11!text],
> null as [treenode!12!text],
> null as [treenode!13!text]
> From @.EmpToMan as t1
> inner join @.EmpToMan as t2
> on t1.ResourceID = t2.ManagerID
> inner join @.EmpToMan as t3
> on t2.ResourceID = t3.ManagerID
> inner join @.EmpToMan as t4
> on t3.ResourceID = t4.ManagerID
> inner join @.EmpToMan as t5
> on t4.ResourceID = t5.ManagerID
> inner join @.EmpToMan as t6
> on t5.ResourceID = t6.ManagerID
> where t1.depth +1 = 1
> and t2.depth + 1 = 2
> and t3.depth + 1 = 3
> and t4.depth + 1 = 4
> and t5.depth + 1 = 5
> and t6.depth + 1 = 6
> --End Block
> union all
> --Begin Block #8
> select
> 8 as Tag,
> 7 as Parent,
> null,
> t1.RLastName,
> t2.RLastName,
> t3.RLastName as [treenode!4!text],
> t4.RLastName as [treenode!5!text],
> t5.RLastName as [treenode!6!text],
> t6.RLastName as [treenode!7!text],
> t7.RLastName as [treenode!8!text],
> null as [treenode!9!text],
> null as [treenode!10!text],
> null as [treenode!11!text],
> null as [treenode!12!text],
> null as [treenode!13!text]
> From @.EmpToMan as t1
> inner join @.EmpToMan as t2
> on t1.ResourceID = t2.ManagerID
> inner join @.EmpToMan as t3
> on t2.ResourceID = t3.ManagerID
> inner join @.EmpToMan as t4
> on t3.ResourceID = t4.ManagerID
> inner join @.EmpToMan as t5
> on t4.ResourceID = t5.ManagerID
> inner join @.EmpToMan as t6
> on t5.ResourceID = t6.ManagerID
> inner join @.EmpToMan as t7
> on t6.ResourceID = t7.ManagerID
> where t1.depth +1 = 1
> and t2.depth + 1 = 2
> and t3.depth + 1 = 3
> and t4.depth + 1 = 4
> and t5.depth + 1 = 5
> and t6.depth + 1 = 6
> and t7.depth + 1 = 7
> --End Block
> union all
> --Begin Block #9
> select
> 9 as Tag,
> 8 as Parent,
> null,
> t1.RLastName,
> t2.RLastName,
> t3.RLastName as [treenode!4!text],
> t4.RLastName as [treenode!5!text],
> t5.RLastName as [treenode!6!text],
> t6.RLastName as [treenode!7!text],
> t7.RLastName as [treenode!8!text],
> t8.RLastName as [treenode!9!text],
> null as [treenode!10!text],
> null as [treenode!11!text],
> null as [treenode!12!text],
> null as [treenode!13!text]
> From @.EmpToMan as t1
> inner join @.EmpToMan as t2
> on t1.ResourceID = t2.ManagerID
> inner join @.EmpToMan as t3
> on t2.ResourceID = t3.ManagerID
> inner join @.EmpToMan as t4
> on t3.ResourceID = t4.ManagerID
> inner join @.EmpToMan as t5
> on t4.ResourceID = t5.ManagerID
> inner join @.EmpToMan as t6
> on t5.ResourceID = t6.ManagerID
> inner join @.EmpToMan as t7
> on t6.ResourceID = t7.ManagerID
> inner join @.EmpToMan as t8
> on t7.ResourceID = t8.ManagerID
> where t1.depth +1 = 1
> and t2.depth + 1 = 2
> and t3.depth + 1 = 3
> and t4.depth + 1 = 4
> and t5.depth + 1 = 5
> and t6.depth + 1 = 6
> and t7.depth + 1 = 7
> and t8.depth + 1 = 8
> --End Block
> union all
>
> --Begin Block #10
> select
> 10 as Tag,
> 9 as Parent,
> null,
> t1.RLastName,
> t2.RLastName,
> t3.RLastName as [treenode!4!text],
> t4.RLastName as [treenode!5!text],
> t5.RLastName as [treenode!6!text],
> t6.RLastName as [treenode!7!text],
> t7.RLastName as [treenode!8!text],
> t8.RLastName as [treenode!9!text],
> t9.RLastName as [treenode!10!text],
> null as [treenode!11!text],
> null as [treenode!12!text],
> null as [treenode!13!text]
> From @.EmpToMan as t1
> inner join @.EmpToMan as t2
> on t1.ResourceID = t2.ManagerID
> inner join @.EmpToMan as t3
> on t2.ResourceID = t3.ManagerID
> inner join @.EmpToMan as t4
> on t3.ResourceID = t4.ManagerID
> inner join @.EmpToMan as t5
> on t4.ResourceID = t5.ManagerID
> inner join @.EmpToMan as t6
> on t5.ResourceID = t6.ManagerID
> inner join @.EmpToMan as t7
> on t6.ResourceID = t7.ManagerID
> inner join @.EmpToMan as t8
> on t7.ResourceID = t8.ManagerID
> inner join @.EmpToMan as t9
> on t8.ResourceID = t9.ManagerID
> where t1.depth +1 = 1
> and t2.depth + 1 = 2
> and t3.depth + 1 = 3
> and t4.depth + 1 = 4
> and t5.depth + 1 = 5
> and t6.depth + 1 = 6
> and t7.depth + 1 = 7
> and t8.depth + 1 = 8
> and t9.depth + 1 = 9
> --End Block
> union all
> --Begin Block #11
> select
> 11 as Tag,
> 10 as Parent,
> null,
> t1.RLastName,
> t2.RLastName,
> t3.RLastName as [treenode!4!text],
> t4.RLastName as [treenode!5!text],
> t5.RLastName as [treenode!6!text],
> t6.RLastName as [treenode!7!text],
> t7.RLastName as [treenode!8!text],
> t8.RLastName as [treenode!9!text],
> t9.RLastName as [treenode!10!text],
> t10.RLastName as [treenode!11!text],
> null as [treenode!12!text],
> null as [treenode!13!text]
> From @.EmpToMan as t1
> inner join @.EmpToMan as t2
> on t1.ResourceID = t2.ManagerID
> inner join @.EmpToMan as t3
> on t2.ResourceID = t3.ManagerID
> inner join @.EmpToMan as t4
> on t3.ResourceID = t4.ManagerID
> inner join @.EmpToMan as t5
> on t4.ResourceID = t5.ManagerID
> inner join @.EmpToMan as t6
> on t5.ResourceID = t6.ManagerID
> inner join @.EmpToMan as t7
> on t6.ResourceID = t7.ManagerID
> inner join @.EmpToMan as t8
> on t7.ResourceID = t8.ManagerID
> inner join @.EmpToMan as t9
> on t8.ResourceID = t9.ManagerID
> inner join @.EmpToMan as t10
> on t9.ResourceID = t10.ManagerID
> where t1.depth +1 = 1
> and t2.depth + 1 = 2
> and t3.depth + 1 = 3
> and t4.depth + 1 = 4
> and t5.depth + 1 = 5
> and t6.depth + 1 = 6
> and t7.depth + 1 = 7
> and t8.depth + 1 = 8
> and t9.depth + 1 = 9
> and t10.depth + 1 = 10
> --End Block
> union all
> --Begin Block #12
> select
> 12 as Tag,
> 11 as Parent,
> null,
> t1.RLastName,
> t2.RLastName,
> t3.RLastName as [treenode!4!text],
> t4.RLastName as [treenode!5!text],
> t5.RLastName as [treenode!6!text],
> t6.RLastName as [treenode!7!text],
> t7.RLastName as [treenode!8!text],
> t8.RLastName as [treenode!9!text],
> t9.RLastName as [treenode!10!text],
> t10.RLastName as [treenode!11!text],
> t11.RLastName as [treenode!12!text],
> null as [treenode!13!text]
> From @.EmpToMan as t1
> inner join @.EmpToMan as t2
> on t1.ResourceID = t2.ManagerID
> inner join @.EmpToMan as t3
> on t2.ResourceID = t3.ManagerID
> inner join @.EmpToMan as t4
> on t3.ResourceID = t4.ManagerID
> inner join @.EmpToMan as t5
> on t4.ResourceID = t5.ManagerID
> inner join @.EmpToMan as t6
> on t5.ResourceID = t6.ManagerID
> inner join @.EmpToMan as t7
> on t6.ResourceID = t7.ManagerID
> inner join @.EmpToMan as t8
> on t7.ResourceID = t8.ManagerID
> inner join @.EmpToMan as t9
> on t8.ResourceID = t9.ManagerID
> inner join @.EmpToMan as t10
> on t9.ResourceID = t10.ManagerID
> inner join @.EmpToMan as t11
> on t10.ResourceID = t11.ManagerID
> where t1.depth +1 = 1
> and t2.depth + 1 = 2
> and t3.depth + 1 = 3
> and t4.depth + 1 = 4
> and t5.depth + 1 = 5
> and t6.depth + 1 = 6
> and t7.depth + 1 = 7
> and t8.depth + 1 = 8
> and t9.depth + 1 = 9
> and t10.depth + 1 = 10
> and t11.depth + 1 = 11
> --End Block
> union all
> --Begin Block #13
> select
> 13 as Tag,
> 12 as Parent,
> null,
> t1.RLastName,
> t2.RLastName,
> t3.RLastName as [treenode!4!text],
> t4.RLastName as [treenode!5!text],
> t5.RLastName as [treenode!6!text],
> t6.RLastName as [treenode!7!text],
> t7.RLastName as [treenode!8!text],
> t8.RLastName as [treenode!9!text],
> t9.RLastName as [treenode!10!text],
> t10.RLastName as [treenode!11!text],
> t11.RLastName as [treenode!12!text],
> t12.RLastName as [treenode!13!text]
> From @.EmpToMan as t1
> inner join @.EmpToMan as t2
> on t1.ResourceID = t2.ManagerID
> inner join @.EmpToMan as t3
> on t2.ResourceID = t3.ManagerID
> inner join @.EmpToMan as t4
> on t3.ResourceID = t4.ManagerID
> inner join @.EmpToMan as t5
> on t4.ResourceID = t5.ManagerID
> inner join @.EmpToMan as t6
> on t5.ResourceID = t6.ManagerID
> inner join @.EmpToMan as t7
> on t6.ResourceID = t7.ManagerID
> inner join @.EmpToMan as t8
> on t7.ResourceID = t8.ManagerID
> inner join @.EmpToMan as t9
> on t8.ResourceID = t9.ManagerID
> inner join @.EmpToMan as t10
> on t9.ResourceID = t10.ManagerID
> inner join @.EmpToMan as t11
> on t10.ResourceID = t11.ManagerID
> inner join @.EmpToMan as t12
> on t11.ResourceID = t12.ManagerID
> where t1.depth +1 = 1
> and t2.depth + 1 = 2
> and t3.depth + 1 = 3
> and t4.depth + 1 = 4
> and t5.depth + 1 = 5
> and t6.depth + 1 = 6
> and t7.depth + 1 = 7
> and t8.depth + 1 = 8
> and t9.depth + 1 = 9
> and t10.depth + 1 = 10
> and t11.depth + 1 = 11
> and t12.depth + 1 = 12
> --End Block
> order by
> [TREENODES!1!text],
> [treenode!2!text],
> [treenode!3!text],
> [treenode!4!text],
> [treenode!5!text],
> [treenode!6!text],
> [treenode!7!text],
> [treenode!8!text],
> [treenode!9!text],
> [treenode!10!text],
> [treenode!11!text],
> [treenode!12!text],
> [treenode!13!text]
> for xml explicit
|||Hi Michael,
Yes it is esacape character actually shown like this "\" as part of
data,
The ascii value of it is 34. In the debugger it shows as '"'. I need
to find out how to replace '"'with " double quotes.
I was able to replace with single quotes and empty strings. I am
getting
syntax errors while replacing with double quote.
for example tempstr=Regex.Replace(tempstr,Convert.ToString('"' ),"")
would produce the result some thing like this - text=A/L ALLANDOO,
FELIX\
But I need the result as look like this - text="A/L ALLANDOO, FELIX"\
Looking for some help.
Thanks
Rao
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message news:<#HUyLPyiEHA.2140@.TK2MSFTNGP15.phx.gbl>...[vbcol=seagreen]
> This looks like a C# issue. Are you sure that the \ is actually part of the
> data? It could be that you see \" as a way to escape the " for some C#
> specific reason and that the data still only contains ". You may want to
> check in a C# specific newsgroup if that does not explain it.
> HTH
> Michael
> "rao" <jayamallik@.hotmail.com> wrote in message
> news:f2ff1cc.0408251644.2bf22b62@.posting.google.co m...

FOR XML clause and <root> element

Hi,
When I run the following query in Query Analyzer, it works fine, but I
cannot add <ROOT> ... </ROOT> element to result:
SELECT * FROM
(
select top 10
CompanyName,
SUM((CONVERT(money,("Order
Details".UnitPrice*Quantity*(1-Discount)/100))*100)) AS Amount
, count(*) OrderItemCount
from
customers
inner join orders on orders.CustomerID = customers.CustomerID
inner join [Order Details] on Orders.OrderID = [Order Details].OrderID
GROUP BY
CompanyName
) AS GoodCustomers
FOR XML AUTO ,ELEMENTS
Ho can add <root> element to the result?
Thank you,
AlanHi
You can do that externally such as http://sqlxml.org/faqs.aspx?faq=10
This may also help http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp
John
"A.M" wrote:

>
> Hi,
>
> When I run the following query in Query Analyzer, it works fine, but I
> cannot add <ROOT> ... </ROOT> element to result:
>
>
> SELECT * FROM
> (
> select top 10
> CompanyName,
> SUM((CONVERT(money,("Order
> Details".UnitPrice*Quantity*(1-Discount)/100))*100)) AS Amount
> , count(*) OrderItemCount
> from
> customers
> inner join orders on orders.CustomerID = customers.CustomerID
> inner join [Order Details] on Orders.OrderID = [Order Details].OrderID
> GROUP BY
> CompanyName
> ) AS GoodCustomers
> FOR XML AUTO ,ELEMENTS
>
>
>
>
>
> Ho can add <root> element to the result?
>
> Thank you,
> Alan
>
>|||Hi,
Additional to what John recommanded, there is a new property that you can
use to tell the OLEDB provider to add
the root tag for you.
command.Properties("Output Stream") = response
command.Properties("XML Root") = "root"
command.Execute , , 1024
Then the stream you are getting back will have "XML Root" as the document
root tag.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

FOR XML AUTO, ELEMENTS

I am using SQL Server 2000.
When I run SQL in Query Analyzer similar to the following, each row in the
results is truncated to 256 characters:
SELECT * FROM tblName
FOR XML AUTO, ELEMENTS
How can I prevent the output from truncating each row?
Also, is it possible for the output to be formatted with a CRLF after each
element, and appropriate indentation of elements?
Thanks
Bill
First, you can increase the limit of the result to 4000 characters. That way
you will see everything. However, since the query analyzer does not really
understand the XML, you should not attempt to use the XML there except for
doing visual checks. If you want to get the XML in a stream, use either the
ADO/ADO.Net mechanisms to get the XML stream back or use the SQLXML ISAPI.
In the later case, you would access the data through IE, and thus you would
get your pretty-printing of the XML.
Best regards
Michael
"bill" <belgie@.datamti.com> wrote in message
news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>I am using SQL Server 2000.
> When I run SQL in Query Analyzer similar to the following, each row in the
> results is truncated to 256 characters:
> SELECT * FROM tblName
> FOR XML AUTO, ELEMENTS
> How can I prevent the output from truncating each row?
> Also, is it possible for the output to be formatted with a CRLF after each
> element, and appropriate indentation of elements?
> Thanks
> Bill
>
|||I always change my settings to display 8192 instead of the annoying 256
standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
> First, you can increase the limit of the result to 4000 characters. That
> way you will see everything. However, since the query analyzer does not
> really understand the XML, you should not attempt to use the XML there
> except for doing visual checks. If you want to get the XML in a stream,
> use either the ADO/ADO.Net mechanisms to get the XML stream back or use
> the SQLXML ISAPI. In the later case, you would access the data through IE,
> and thus you would get your pretty-printing of the XML.
> Best regards
> Michael
> "bill" <belgie@.datamti.com> wrote in message
> news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>
|||Too much is not a problem. The reason why I said 4000 is that each stream
block (ie a row chunk) that is being returned is around 2034 bytes, so 4000
is enough).
But regardless of the setting, if you get the XML in more than one chunk in
the query analyzer, you will have to do some postprocessing to get rid of
the newlines.
Best regards
Mcihael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I always change my settings to display 8192 instead of the annoying 256
>standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
>
|||Yes a bit annoying that you have to do that yourself instead of checking
that "Don't give me irrelevant New-Lines"-CheckBox in QA. It would have
been nicer without this setting and that QA automatically gave you the
correct line length and that stream blocks were automatically appended as
well. I don't know any people who want them divided this way. Divided
between the tags works fine, but not like this in the middle after 2034
bytes.
I have found EmEditor to be very useful in these cases with its RegExp Find
& Replace: \r\n -> <nothing>.
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
> Too much is not a problem. The reason why I said 4000 is that each stream
> block (ie a row chunk) that is being returned is around 2034 bytes, so
> 4000 is enough).
> But regardless of the setting, if you get the XML in more than one chunk
> in the query analyzer, you will have to do some postprocessing to get rid
> of the newlines.
> Best regards
> Mcihael
> "Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
> news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
|||Well, yes. The SQL Server 2005 integration is now much better, we even have
a hyperlink triggered XML editor build in now.
So go out and upgrade :-).
Best regards
Michael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23927VrQAGHA.832@.tk2msftngp13.phx.gbl...
> Yes a bit annoying that you have to do that yourself instead of checking
> that "Don't give me irrelevant New-Lines"-CheckBox in QA. It would have
> been nicer without this setting and that QA automatically gave you the
> correct line length and that stream blocks were automatically appended as
> well. I don't know any people who want them divided this way. Divided
> between the tags works fine, but not like this in the middle after 2034
> bytes.
> I have found EmEditor to be very useful in these cases with its RegExp
> Find & Replace: \r\n -> <nothing>.
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
>