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.