getting a set of values from xml

i have imported xml into an xml datatype variable. here is a tiny version of my xml file.

<Root>

<TOP>

<USERS>

<USER>

<USER>

<USERNAME>jukkaw</USERNAME>

</USER>

<USER>

<USERNAME>v-derekn</USERNAME>

</USER>

</USERS>

</TOP>

</Root>'

I need to pullout just the username, so the query method is out as it will return it in xml format. how do i just get a column containing all of the usernames?

You could use nodes table-value function:

create table #xml_table

(

xml_col xml

)

go

insert into #xml_table values('<Root>

<TOP>

<USERS>

<USER>

<USERNAME>jukkaw</USERNAME>

</USER>

<USER>

<USERNAME>v-derekn</USERNAME>

</USER>

</USERS>

</TOP>

</Root>

')

select x.value('.[1]','varchar(100)')

from #xml_table t cross apply xml_col.nodes('/Root/TOP/USERS/USER/USERNAME/text()') as tab(x)

Getting "extremely complex query" error when using xml data type parameters

Good morning, I am trying to figure out, and understand, why I am receiving the error that I am when I use xml data type parameters. If you look at the proc below, I have a proc with 8 xml data type parameters. When i call this proc adn pass in the values for the parameters, I recieve is the following:

The query processor ran out of internal resources and could not produce a query plan.

This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions.

Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

In debugging, I found that I DON'T get this error if i have only 3 or 4 xml data type parameters, but if I add more than 4, I get the above error.

The proc looks like the following:

CREATE PROCEDURE dbo.dosomething

@.BegDate DateTime,

@.EndDate DateTime,

@.InClass xml,

@.InDept xml,

@.InCat xml,

@.InPayPer xml,

@.InEmp xml,

@.InLoc xml,

@.InLeave xml,

@.InSalClass xml

AS

SELECT blah, blah

FROM blah

WHERE table.column IN (select [JoinValues].[ref].value('@.id', 'int') from @.InEmp.nodes('ids/values') JoinValues([ref])

AND...join other xml datatypes just like the one above

I call the stored proc by the following...and notice that i am not passing anything very large for each value...

exec dosomething

'20060101','20060101',

N'<ids>

<values id="1" />

<values id="3" />

</ids>'

,N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>'

Does anyone have any suggestions as to what might be causing this? Am i doign my compare in my proc incorrectly? Any insight would be greatly appreciated...

Scott

Hi Scott

This error message normally means that you should rewrite your query. Could you please post a complete repro to either my work address (mrys at the microsoft.com address) or post it here and we will take a look.

Thanks

Michael

PS: Sorry I thought I posted this reply yesterday but my travel connectivity at the moment seems to be shakey.

|||

I guess the problem is not you have lots xml data type parameters. It's caused by the WHERE clause in your query :

"WHERE table.column IN (select [JoinValues].[ref].value('@.id', 'int') from @.InEmp.nodes('ids/values') JoinValues([ref])

AND...join other xml datatypes just like the one above"

You can try to define some temp tables and shred the ID values in the xml into those tables. Jojn those temp tables in your query.

getdata.xml vs getdata.asp

As I've started to use XML more extensively for data transfer to web
pages, I have started to use the XMLHttpRequest object to get data
from the server. Since I want the data to be returned as XML, I have
been playing around with http: requests to SQL Server. I have also
created ASP pages that return XML after using SQL Server to collect
data.
As far as I know, these two methods are approximately equivalent. ASP
clearly has some important advantages over SQL Http requests,
maintaining session information immediately comes to mind, but does
the convenience of ASP come at a high enough cost that I should
consider using SQL Http requests?
I should clarify the question: I'm your typical geek who loves to
learn and use new technology: I'm inclined to use SQL Http requests
simply because they are new and different and I like how they can help
delegate different logical tasks in my project. Am I wasting my
client's money and my own time looking to SQL Http requests for
performance improvements?
TIA for any light you can shed on this...
Chuck Jungmann
"Chuck Jungmann" <NOchuckSPAM@.cpjj.net> wrote in message
news:frepj0ddqr0m68kt7rak5og95lv4dfg3j6@.4ax.com...
[snip]
> Am I wasting my
> client's money and my own time looking to SQL Http requests for
> performance improvements?
As with all technology, most things are a compromise. SQLXML is no
exception. I would suggest looking at the pros and cons and then choosing
the method that best suits your needs.
SQLXML vs. ASP pros:
1) Less layers (possible speed increase)
2) Not scripted (possible speed increase)
3) SQL and XML knowledge only
SQLXML vs. ASP cons:
1) Less flexible.
2) Possibly more vunerable to attacks (less testing and real-world use than
ASP)
3) Upgrade path somewhat unclear at the moment (although it looks like 2005
will provide a good upgrade path to SQLXML HTTP).
I would also suggest doing some performance tests and reliability tests to
see how your two methods measure up. I don't think your testing is a waste
of time.
Bryant

Get XML root name in Sql Server

declare @.x xml
set @.x =
'<Chicago>
<Area>A1</Area>
<Group>5</Group>
<Question>Q1</Question>
</Chicago>'

How to get the XML root name 'Chicago' in Sql Server 2005?

Since you are using the XQuery or XPath query you should know the exact path of the xml. Otherwise you can't utilize this new feature in SQL Server...

There is no functions available in XQuery to fetch the XML Root element name, but you can utilize the Stringmanipulation to find what is your root..

Code Snippet

declare @.x xml

set @.x =

'<Chicago>

<Area>A1</Area>

<Group>5</Group>

<Question>Q1</Question>

</Chicago>'

Select

Substring(XML,Charindex('<', XML)+1, Charindex('>', XML) - Charindex('<', XML)-1)

From

(

Select

Cast(@.X as Varchar(max)) as XML

) as Data

Get XML On Its Merry Way

Apologies ahead of time for XML newbie...
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lq
Hello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>

Get XML On Its Merry Way

Apologies ahead of time for XML newbie...
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lqHello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>

Get XML node as 'text' data type

We are using XML to pump data into a SQ: Server 2005 database. We pass an XML document into a stored procedure, the stored procedures chunks out the data and inserts it into the appropriate tables. Fine. Works great, easy to maintain, excelent performance. Here's the problem. One of the columns we are pushing data into is of data type text and the .value fuction of the XML node does not support conversion of a node's data to the 'text' data type. For example:

CREATE PROCEDURE as MyProcedure @.myData xml
BEGIN
INSERT INTO MyTable (FirstName, LastName, Notes)
SELECT
MyNode.value('FirstName[1]','varchar(50)'),
MyNode.value('LastName[1]','varchar(100)'),
MyNode.value('Notes[1]','text')
FROM @.myData.Notes('Person') as R(MyNode)
END

The problem is with the notes field. The cast to the data type text fails with the following error:
The data type 'text' used in the VALUE method is invalid.

The workaround thus far has been to use varchar(8000), but it will result in truncation if the data is too long.

Any ideas?

Try using 'varchar(max)' instead of 'text'

|||Perfect. Thanks!|||What would be a datatype for the value for an image? Will varchar(max) work for it as well?

Get XML node as 'text' data type

We are using XML to pump data into a SQ: Server 2005 database. We pass an XML document into a stored procedure, the stored procedures chunks out the data and inserts it into the appropriate tables. Fine. Works great, easy to maintain, excelent performance. Here's the problem. One of the columns we are pushing data into is of data type text and the .value fuction of the XML node does not support conversion of a node's data to the 'text' data type. For example:

CREATE PROCEDURE as MyProcedure @.myData xml
BEGIN
INSERT INTO MyTable (FirstName, LastName, Notes)
SELECT
MyNode.value('FirstName[1]','varchar(50)'),
MyNode.value('LastName[1]','varchar(100)'),
MyNode.value('Notes[1]','text')
FROM @.myData.Notes('Person') as R(MyNode)
END

The problem is with the notes field. The cast to the data type text fails with the following error:
The data type 'text' used in the VALUE method is invalid.

The workaround thus far has been to use varchar(8000), but it will result in truncation if the data is too long.

Any ideas?

Try using 'varchar(max)' instead of 'text'

|||Perfect. Thanks!|||What would be a datatype for the value for an image? Will varchar(max) work for it as well?

Get XML from SQL Server 2000

I have a stored procedure, that returns a customer record from the customers table in the northwind database.
how can i return back an xml string of the row?
I mean, when the aspx page calls that procudure, I want to have somehting like:
<customers>
<customer>
<customerid>xxx</customerid>
<companyname>rrr</companyname>
</customer>
</customers>
can a stored procedure return such a string in xml form?
thanks alot

Read up on the FOR XML clause. Here's an article:http://www.sqljunkies.com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E.scuk and there's quite a bit of good info in BOL.

Get the XML out of sql server 2005 in c#

Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it?
Pls reply as soon as possible.
Cheers
Hi
You may find something at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:

> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it?
> Pls reply as soon as possible.
> Cheers
>

Get the XML out of sql server 2005 in c#

Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it'
Pls reply as soon as possible.
CheersHi
You may find something at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:
> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it'
> Pls reply as soon as possible.
> Cheers
>

Get the XML out of sql server 2005 in c#

Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it'
Pls reply as soon as possible.
CheersHi
You may find something at
http://www.perfectxml.com/Articles/...ortSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:

> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it'
> Pls reply as soon as possible.
> Cheers
>

get SQL connection for SQL config from XML

Hi

In toder to make my SSIS packages portable, I need to be able to set connection string to sql package configuration. I thought I can do that via XML package, problem is, at execution time SQL server package configuration is queried first, then variables from XML.
This way, I have no chance to set connection via XML.
Any ideas on how to aproach this ?
(I want to have as much configuration on SQL server so I can modify it easily from future GUI application.

best regards

For this scenario what you need is an indirect configuration, where the connection string to the SQL Server database where your "real" configurations are stored is in turn stored in an environment variable:

http://dotnetjunkies.com/WebLog/appeng/archive/2006/05/30/indirectconfigpackagessis.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

I do not know of any way to perform this indirection through an intermediary XML config file - only environment variables are supported.

|||

One more method:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

|||You should be able to set your initial SQL Server connection from an XML configuration file. In the Configurations dialog, make sure that the XML configuration is at the top of the list, so that it is executed first.

Get rows fields as xml name value pairs

Hi,
I'm trying to write a procedure to return the data from a query as xml
in the following format:
<root>
<row>
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
</row>
<row>
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
</row>
<row>
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
<Field Name="[FieldName]" Value="[FieldValue]" />
</row>
</root>
so basically i need to turn the fields of a row into new rows?
I want to send them to a waiting app for deserialization into an
object but want the object to able to deserialize the data whatever it
is.
Any ideas?
Thanks,
George
Your best bet is probably to use an AUTO mode query and then apply an XSLT
transform.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"george" <8eu1ukg02@.sneakemail.com> wrote in message
news:d90f7cf1.0405270433.27d2fdbd@.posting.google.c om...
> Hi,
> I'm trying to write a procedure to return the data from a query as xml
> in the following format:
> <root>
> <row>
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> </row>
> <row>
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> </row>
> <row>
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> <Field Name="[FieldName]" Value="[FieldValue]" />
> </row>
> </root>
> so basically i need to turn the fields of a row into new rows?
> I want to send them to a waiting app for deserialization into an
> object but want the object to able to deserialize the data whatever it
> is.
> Any ideas?
> Thanks,
> George

Get rid of column name as tag when query for xml

here is my query (Email is of type XML):
Select Email
From EmailStorage
Where Status=1
For XML Auto
This returns:
<EmailStorage>
<Email><value of Email></Email>
<Email><value of Email></Email>
</EmailStorage>
Since <value of Email> is valid XML I would like to have this:
<EmailStorage>
<value of Email>
<value of Email>
</EmailStorage>
How? TIA!Art wrote:
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
Does
SELECT (
SELECT [Email].query('.')
FROM EmailStorage
WHERE Status = 1
FOR XML PATH, TYPE
).query('<EmailStorage>{row/node()}</EmailStorage>')
do what you want?
It is a bit convoluted but currently I can't think of an easier way.
Maybe someone else will come up with an easier query.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Ohh yes my man! It did work just as expected thou performance was a bit slow
.
I'll test it on another server to see it that's query issue or stress testin
g
they might be doing on the server. thanks!
"Martin Honnen" wrote:

> Art wrote:
> Does
> SELECT (
> SELECT [Email].query('.')
> FROM EmailStorage
> WHERE Status = 1
> FOR XML PATH, TYPE
> ).query('<EmailStorage>{row/node()}</EmailStorage>')
> do what you want?
> It is a bit convoluted but currently I can't think of an easier way.
> Maybe someone else will come up with an easier query.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
>|||Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
> How? TIA!
>|||I read your post with similiar suggestions you gave someone else but couldn'
t
make it work. This one worked great. Thanks!
Now, I need to make this query work with SQL Adapter for biztalk. First time
the wizard is run (to generate the schema for this document) I need to
specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot of
problems with that.
To recap; I'd need something like this (pseudo code)
<Your Query> For XML Auto, XMLData
How can I do this?
"Pawel Potasinski" wrote:

> Select Email '*'
> From EmailStorage
> Where Status=1
> For XML path(''), root('EmailStorage')
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
>
>|||What is the goal exactly? Is you goal just adding XML Schema inline to the
query result? If so, let me know. If you really have to use FOR XML AUTO,
there will be a problem to get the result just as you received from my
prevous query.
Oh, and BTW, the next question is: should XML Schema include elements and
attributes of Email column (of xml data type) or should this column be put
in XSD just as xml data type element (but no internal structure of Email
data will be shown)?
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...
>I read your post with similiar suggestions you gave someone else but
>couldn't
> make it work. This one worked great. Thanks!
> Now, I need to make this query work with SQL Adapter for biztalk. First
> time
> the wizard is run (to generate the schema for this document) I need to
> specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot
> of
> problems with that.
> To recap; I'd need something like this (pseudo code)
> <Your Query> For XML Auto, XMLData
> How can I do this?
> "Pawel Potasinski" wrote:
>|||The goal here is to make your query work with SQL Adapter (in BizTalk).
Surprisingly, even though this is MS technology and works with XML, SQL
Adapter is rather primitive. One of the first steps in configuration (of the
SQL adapter) is formulating the query so that the adapter configuration
wizard can create a schema based on the output of a query. Two directives
need to be specified in order for the configuration wizard to work, namely
FOR XML AUTO, XMLDATA.
Now, when you look at your query
Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
How do I incorporate FOR XML AUTO, XMLDATA so that it works with the SQL
Adapter.
"Pawel Potasinski" wrote:

> What is the goal exactly? Is you goal just adding XML Schema inline to the
> query result? If so, let me know. If you really have to use FOR XML AUTO,
> there will be a problem to get the result just as you received from my
> prevous query.
> Oh, and BTW, the next question is: should XML Schema include elements and
> attributes of Email column (of xml data type) or should this column be put
> in XSD just as xml data type element (but no internal structure of Email
> data will be shown)?
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...
>
>|||Pawel,
BTW, can your query be modified to accept a parameter?
From:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root('EmailStorage')
To:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root(@.SomeStatus)
I tried it but it doesn't work.

Get rid of column name as tag when query for xml

here is my query (Email is of type XML):
Select Email
From EmailStorage
Where Status=1
For XML Auto
This returns:
<EmailStorage>
<Email><value of Email></Email>
<Email><value of Email></Email>
</EmailStorage>
Since <value of Email> is valid XML I would like to have this:
<EmailStorage>
<value of Email>
<value of Email>
</EmailStorage>
How? TIA!
Art wrote:
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
Does
SELECT (
SELECT [Email].query('.')
FROM EmailStorage
WHERE Status = 1
FOR XML PATH, TYPE
).query('<EmailStorage>{row/node()}</EmailStorage>')
do what you want?
It is a bit convoluted but currently I can't think of an easier way.
Maybe someone else will come up with an easier query.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Ohh yes my man! It did work just as expected thou performance was a bit slow.
I'll test it on another server to see it that's query issue or stress testing
they might be doing on the server. thanks!
"Martin Honnen" wrote:

> Art wrote:
> Does
> SELECT (
> SELECT [Email].query('.')
> FROM EmailStorage
> WHERE Status = 1
> FOR XML PATH, TYPE
> ).query('<EmailStorage>{row/node()}</EmailStorage>')
> do what you want?
> It is a bit convoluted but currently I can't think of an easier way.
> Maybe someone else will come up with an easier query.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
>
|||Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
> How? TIA!
>
|||I read your post with similiar suggestions you gave someone else but couldn't
make it work. This one worked great. Thanks!
Now, I need to make this query work with SQL Adapter for biztalk. First time
the wizard is run (to generate the schema for this document) I need to
specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot of
problems with that.
To recap; I'd need something like this (pseudo code)
<Your Query> For XML Auto, XMLData
How can I do this?
"Pawel Potasinski" wrote:

> Select Email '*'
> From EmailStorage
> Where Status=1
> For XML path(''), root('EmailStorage')
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
>
>
|||What is the goal exactly? Is you goal just adding XML Schema inline to the
query result? If so, let me know. If you really have to use FOR XML AUTO,
there will be a problem to get the result just as you received from my
prevous query.
Oh, and BTW, the next question is: should XML Schema include elements and
attributes of Email column (of xml data type) or should this column be put
in XSD just as xml data type element (but no internal structure of Email
data will be shown)?
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...[vbcol=seagreen]
>I read your post with similiar suggestions you gave someone else but
>couldn't
> make it work. This one worked great. Thanks!
> Now, I need to make this query work with SQL Adapter for biztalk. First
> time
> the wizard is run (to generate the schema for this document) I need to
> specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot
> of
> problems with that.
> To recap; I'd need something like this (pseudo code)
> <Your Query> For XML Auto, XMLData
> How can I do this?
> "Pawel Potasinski" wrote:
|||The goal here is to make your query work with SQL Adapter (in BizTalk).
Surprisingly, even though this is MS technology and works with XML, SQL
Adapter is rather primitive. One of the first steps in configuration (of the
SQL adapter) is formulating the query so that the adapter configuration
wizard can create a schema based on the output of a query. Two directives
need to be specified in order for the configuration wizard to work, namely
FOR XML AUTO, XMLDATA.
Now, when you look at your query
Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
How do I incorporate FOR XML AUTO, XMLDATA so that it works with the SQL
Adapter.
"Pawel Potasinski" wrote:

> What is the goal exactly? Is you goal just adding XML Schema inline to the
> query result? If so, let me know. If you really have to use FOR XML AUTO,
> there will be a problem to get the result just as you received from my
> prevous query.
> Oh, and BTW, the next question is: should XML Schema include elements and
> attributes of Email column (of xml data type) or should this column be put
> in XSD just as xml data type element (but no internal structure of Email
> data will be shown)?
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...
>
>
|||Pawel,
BTW, can your query be modified to accept a parameter?
From:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root('EmailStorage')
To:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root(@.SomeStatus)
I tried it but it doesn't work.

Get RDL from report server please help!

Hi,

I was wondering if it is possible to get the rdl or xml from the reporting server some how for a report that is currently published to the server? One my coworkers deleted a report out of a separate project, but somehow it also deleted the report out of the production project. I have older copies of the report, but I would really like to pull down the latest version.

Any ideas?

Thanks,

S

Hello S,

Yes, you can do this. Navigate to the report in your Report Manager (http://ServerName/Reports). Go to properties, then click the Edit link under "Report Definition". It will ask you to save the RDL file.

Hope this helps.

Jarret

|||

Jarret,

You are a life saver thank you!

S