ForEach File Enumerator extension bug?

I set up a basic ForEach enumerator loop and specified files of type *.sql.

In the directory, I had some files I needed to keep but didn't want the package to touch, so I changed the extension to *.sqlo. Much to my dismay, the ForEach loop picked up those files. (Though it did skip the *.xml and *.bat and a few other types...)

Sounds like a bug to me.

That is expected wildcard behaviour. At least, it is what I would expect. To verify I wasn't 'shrooming, I checked out: http://msdn2.microsoft.com/en-us/library/wz42302f.aspx where I found this to put my mind at ease ...

Note

When using the asterisk wildcard character in a searchPattern, such as "*.txt", the matching behavior when the extension is exactly three characters long is different than when the extension is more or less than three characters long. A searchPattern with a file extension of exactly three characters returns files having an extension of three or more characters, where the first three characters match the file extension specified in the searchPattern. A searchPattern with a file extension of one, two, or more than three characters returns only files having extensions of exactly that length that match the file extension specified in the searchPattern. When using the question mark wildcard character, this method returns only files that match the specified file extension. For example, given two files, "file1.txt" and "file1.txtother", in a directory, a search pattern of "file?.txt" returns just the first file, while a search pattern of "file*.txt" returns both files.

Donald

|||

I believe you (after looking at that link). But that is just bizarre and, to me, nonsensical. Especially if one continues to read. This *only* applies to a 3-character extension search pattern - nothing else.

Why oh why would they do such a stupid thing?

This means that there is direct no way to specify give me only the three character file extension files that I've told you to give me.

Were they shrooming when they came up with this?

If I wanted the other extensions I'd ask for "*.txt*".

Force Excel Column type when exporting from SSRS

Hi all,

I have a tricky behavior here. I have a column in my report which contains alphanumeric codes. When I have a code like 17E001 and I export the report to Excel, excel kindly shows that alphanumeric code to 1+E7 and the value of the column is changed to 1700 which is defintly not what I want.

So I was wondering if there is any way to force the column types when exporting from SSRS?

Sbastien.

By the way if there is a way to force all columns to be formated as Text that will do for me as the excel reports are only used to process data using SSIS.

for xml raw problem

Hi
I'm running a simple query with for xml raw on a table
one of columns in select clause here is of type text and stores xml
file itself.
The problem i'm facing is when I get this text back from this column I
get back unformatted xml
e.g
instead of getting <GPF> I get <GPF> etc
How do I get this column as formatted xml text ?
Thanks for help
Vishy>
> I'm running a simple query with for xml raw on a table
> one of columns in select clause here is of type text and stores xml
> file itself.
> The problem i'm facing is when I get this text back from this column I
> get back unformatted xml
> e.g
> instead of getting <GPF> I get <GPF> etc
> How do I get this column as formatted xml text ?
>
Hello
signs '<' and '>' are very important in XML because they show begin and end
of xml element, so when an element or attribute value has such signs it must
be changed into < and >. But when you open XML which contains such
substitution - using DOM or OPENXML in SQL you will get values with signs
'<', '>'. So I think that it sould not be a problem - siply try to process
XML which was generated and you will see that it has valid values of
elements/attributes.
I hope it helps,
Alwik|||Thanks Alwik for reply
I tried to create object on XmlDocument using LoadXml() method
it loads up the xml successfully but if I want to run any Xpath; it
fails
as <GPF> is not treated as <GPF> so I can't find GPF element
is there any work around?
Vishy|||Are you using SQL Server 2000 or 2005?
In the case of SQL Server 2000, you will have to use the explicit mode and
its !xml directive, if you want to have string data be inlined into the XML
structure.
In the case of SQL Server 2005, you can stick with the FOR XML RAW query,
but you need to cast the string data to XML in the select clause.
Best regards
Michael
"Vishy" <vishal.halbe@.gmail.com> wrote in message
news:1128445808.595093.141180@.g49g2000cwa.googlegroups.com...
> Thanks Alwik for reply
> I tried to create object on XmlDocument using LoadXml() method
> it loads up the xml successfully but if I want to run any Xpath; it
> fails
> as <GPF> is not treated as <GPF> so I can't find GPF element
> is there any work around?
> Vishy
>|||Thanks Michael for reply
I'm using SQL 2000
Vishy

for xml raw problem

Hi
I'm running a simple query with for xml raw on a table
one of columns in select clause here is of type text and stores xml
file itself.
The problem i'm facing is when I get this text back from this column I
get back unformatted xml
e.g
instead of getting <GPF> I get <GPF> etc
How do I get this column as formatted xml text ?
Thanks for help
Vishy
>
> I'm running a simple query with for xml raw on a table
> one of columns in select clause here is of type text and stores xml
> file itself.
> The problem i'm facing is when I get this text back from this column I
> get back unformatted xml
> e.g
> instead of getting <GPF> I get <GPF> etc
> How do I get this column as formatted xml text ?
>
Hello
signs '<' and '>' are very important in XML because they show begin and end
of xml element, so when an element or attribute value has such signs it must
be changed into < and >. But when you open XML which contains such
substitution - using DOM or OPENXML in SQL you will get values with signs
'<', '>'. So I think that it sould not be a problem - siply try to process
XML which was generated and you will see that it has valid values of
elements/attributes.
I hope it helps,
Alwik
|||Thanks Alwik for reply
I tried to create object on XmlDocument using LoadXml() method
it loads up the xml successfully but if I want to run any Xpath; it
fails
as <GPF> is not treated as <GPF> so I can't find GPF element
is there any work around?
Vishy
|||Are you using SQL Server 2000 or 2005?
In the case of SQL Server 2000, you will have to use the explicit mode and
its !xml directive, if you want to have string data be inlined into the XML
structure.
In the case of SQL Server 2005, you can stick with the FOR XML RAW query,
but you need to cast the string data to XML in the select clause.
Best regards
Michael
"Vishy" <vishal.halbe@.gmail.com> wrote in message
news:1128445808.595093.141180@.g49g2000cwa.googlegr oups.com...
> Thanks Alwik for reply
> I tried to create object on XmlDocument using LoadXml() method
> it loads up the xml successfully but if I want to run any Xpath; it
> fails
> as <GPF> is not treated as <GPF> so I can't find GPF element
> is there any work around?
> Vishy
>
|||Thanks Michael for reply
I'm using SQL 2000
Vishy

FOR XML Explicit query

I need to output a query to xml with a Type Coded element, but am not sure how to format it in SQL. The result should look like this:

<Person>

<Name>Test</Name>

<Gender tc="1"/>

</Person>

Any help would be appreciated

Thanks

Stphane

Use this format:

SELECT 'Test' AS 'Person/Name', '1' AS 'Person/Gender/@.tc' FOR XML PATH(''), TYPE;

It will yield the following xml for you.

<Person><Name>Test</Name><Gender tc="1" /></Person>

(Note that "FOR XML PATH" is new in SQL Server 2005)

FOR XML Explicit query

I need to output a query to xml with a Type Coded element, but am not sure how to format it in SQL. The result should look like this:

<Person>

<Name>Test</Name>

<Gender tc="1"/>

</Person>

Any help would be appreciated

Thanks

Stphane

Use this format:

SELECT 'Test' AS 'Person/Name', '1' AS 'Person/Gender/@.tc' FOR XML PATH(''), TYPE;

It will yield the following xml for you.

<Person><Name>Test</Name><Gender tc="1" /></Person>

(Note that "FOR XML PATH" is new in SQL Server 2005)

FOR XML Clause TYPE option

Hello,

Could anybody tell me if it's possible to named the result of this query?

SELECT User_Id, Name, Surname, Age, Nationality
FROM [Basic Data] FOR XML PATH, TYPE, elements, root('AllPrimaryData')

The result of this query is an XML Document in a column without a name and I'm trying to name it but I can't.

Could anybody help me with that?

Thanks a lot.

I'm not sure if this will work, but try:

SELECT

User_Id as "@.UserId",

Name as "@.Name",

Surname as "@.SurName",

Age as "@.Age",

Nationality as "@.Nationality"
FROM [Basic Data]

FOR XML PATH, TYPE, elements, root('AllPrimaryData')|||

Try something like this:

select nameOfColumn from (
SELECT User_Id, Name, Surname, Age, Nationality
FROM [Basic Data] FOR XML PATH, TYPE, elements, root('AllPrimaryData')
as myXMLTable(nameOfColumn)

or do it with a CTE:

with myXMLTable(nameOfColumn) as (
SELECT User_Id, Name, Surname, Age, Nationality
FROM [Basic Data] FOR XML PATH, TYPE, elements, root('AllPrimaryData')
)
select nameOfColumn
from myXMLTable

Steve Kass
Drew University
www.stevekass.com

|||

Hi,

Thanks for your response, but it doesn't work.

|||Thanks a lot Steve, your first proposal works fine!

for passing text type argument to procedure

For parsing data > 8000 char I have defined a procedure that looks something like
create proc myproc @.doc text
as
blah blah blah
go

but now i need to pass text argument to it.
For that I need ::: declare @.newdoc text
and then call ::: exec myproc @.newdoc
but I cannot declare text type variable in the transact sql queries.

Is there a way out
thanksyou normally don't work directly with text, ntext and image data. When working with these types of data you normally pass pointers to the data and use functions/stored procedures to manipulate the data, TEXTPTR, WRITETEXT and UPDATETEXT. Check out "text and image data types" in Books On Line.