Hi, got a problem and can't figure this one out.
basically i've got a field containing a value '(14)(12)(33)(22)' and i want to compare it to a table containing those values in separate cells...
record 1 : (01)
record 2 : (02)
etc...
and i want to compare this one field to those records to see whether that text string contains anything the table contains.
i've tried ContainsTable & IN but still can't figure this out.
any help greatly appreciated
:owould you kindly give us the table names and column names involved
otherwise the most i can tell you is to use LIKE
Showing posts with label containing. Show all posts
Showing posts with label containing. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
Help! Query Question.
Hi all,
I'm new to SQL programming, and I am having a hard time figuring this one ou
t.
I have a table containing the following information:
Activity Cost Account Hours
1 A 100
1 B 200
1 C 250
1 D 100
2 A 600
2 F 200
3 B 100
3 C 200
3 D 400
I would like to create a view that will show the Cost Account with the
highest hours per activity. For the table above, the end result would be
something like this.
Activity Cost Account
1 C
2 A
3 D
I hope this makes sense. Can someone please help me on how to write the SQL
statement for this? Thank you very much in advance!
RogerI'm not sure what you want to do in the case where 2 accounts have the same
number of hours (tied for the most). I'm going to assume you want two rows
returned.
SELECT outer_table.Activity, derived_table.[Cost Account]
From Unnamed_table as outer_table
INNER JOIN
(SELECT Activity, MAX(Hours) as Hours
FROM Unnamed_table
GROUP BY Activity) as derived_table
ON outer_table.Activity = derived_table.Activity
AND outer_table.Hours = derived_table.Hours
ORDER BY Activity
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:
> Hi all,
> I'm new to SQL programming, and I am having a hard time figuring this one
out.
> I have a table containing the following information:
> Activity Cost Account Hours
> 1 A 100
> 1 B 200
> 1 C 250
> 1 D 100
> 2 A 600
> 2 F 200
> 3 B 100
> 3 C 200
> 3 D 400
> I would like to create a view that will show the Cost Account with the
> highest hours per activity. For the table above, the end result would be
> something like this.
> Activity Cost Account
> 1 C
> 2 A
> 3 D
> I hope this makes sense. Can someone please help me on how to write the S
QL
> statement for this? Thank you very much in advance!
> Roger|||Do:
SELECT t1.activity, t1.Cost
FROM tbl t1
WHERE t1.hours = ( SELECT MAX( t2.hours )
FROM tbl t2
WHERE t2.Activity = t1.Activity )
ORDER BY t1.Activity ;
-- Or
SELECT t1.activity, t1.Cost
FROM tbl t1
INNER JOIN ( SELECT Activity, MAX( hours )
FROM tbl
GROUP BY Activity ) t2 ( Activity, hours )
ON t1.Activity = t2.Activity
AND t1.hours = t2.hours
ORDER BY t1.activity ;
Anith|||Hi Anith,
Thank you for the quick response. I am a little
about the SQL
statement. I only have one table and I want to create a view from that
table. But your SQL statement has 2 tables. Can you please clarify? I
think maybe most post wasn't clear. Thank you very much.
Roger
"Anith Sen" wrote:
> Do:
> SELECT t1.activity, t1.Cost
> FROM tbl t1
> WHERE t1.hours = ( SELECT MAX( t2.hours )
> FROM tbl t2
> WHERE t2.Activity = t1.Activity )
> ORDER BY t1.Activity ;
> -- Or
>
> SELECT t1.activity, t1.Cost
> FROM tbl t1
> INNER JOIN ( SELECT Activity, MAX( hours )
> FROM tbl
> GROUP BY Activity ) t2 ( Activity, hours )
> ON t1.Activity = t2.Activity
> AND t1.hours = t2.hours
> ORDER BY t1.activity ;
> --
> Anith
>
>|||Did you try to execute the SQL that either of us posted?
It returns what you want.
Anith gave 2 versions.
The first is called a correlated subquery. But, it basically joining your
table to a sql statement against the same table to figure out the max per
Activity.
The second (which is the same as what I posted) is called a derived table or
inline view. Basically, on the fly create a table that contains the activit
y
and its maximum hours from all rows for that activity. Then join this
derived table with the regular table, to only find the rows that match the
activity and the max hours found in the derived table.
There is no way to do what you want with SQL with only the one table in the
FROM. Instead you need join your table to some sql that gives you some
specific information about your table in this case, you need to figure out
the maximum hours for each activity so that you can figure out the correct
rows to return from your table.
My solution, and both of Anith's solution would work fine as the source of a
view to do exactly what you are trying to do.
Hope this makes sense.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:
> Hi Anith,
> Thank you for the quick response. I am a little about the SQL
> statement. I only have one table and I want to create a view from that
> table. But your SQL statement has 2 tables. Can you please clarify? I
> think maybe most post wasn't clear. Thank you very much.
> Roger
>
> "Anith Sen" wrote:
>|||Hi Ryan,
Thank you for the response. Yes, I want to show both cost accounts if there
is a tie. I am a little about the outer_table and derived_table.
Supposed my table is called t1, how would the SQL be written? Thank you ver
y
much.
Roger
"Ryan Powers" wrote:
> I'm not sure what you want to do in the case where 2 accounts have the sam
e
> number of hours (tied for the most). I'm going to assume you want two row
s
> returned.
> SELECT outer_table.Activity, derived_table.[Cost Account]
> From Unnamed_table as outer_table
> INNER JOIN
> (SELECT Activity, MAX(Hours) as Hours
> FROM Unnamed_table
> GROUP BY Activity) as derived_table
> ON outer_table.Activity = derived_table.Activity
> AND outer_table.Hours = derived_table.Hours
> ORDER BY Activity
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Yoyo" wrote:
>|||just replace unnamed_table with t1.
as in
SELECT outer_table.Activity, derived_table.[Cost Account]
From t1 as outer_table
INNER JOIN
(SELECT Activity, MAX(Hours) as Hours
FROM t1
GROUP BY Activity) as derived_table
ON outer_table.Activity = derived_table.Activity
AND outer_table.Hours = derived_table.Hours
ORDER BY Activity
Also, see my other post to you. I tried to explain the derived table and
Anith's correlated subquery to you. Neither of which is easy to explain in
a
couple sentences if you are new.
But, basically here is another attempt
Think of the table within the () as its own table completely separate from
your t1.
(SELECT Activity, MAX(Hours) as Hours
FROM t1
GROUP BY Activity)
This will give you each activity and the max hours for that activity. One
row per activity.
Now you join your table t1 to that new table (which I called derived_table)
on both activity and hours. This helps you identify the rows in t1 that you
are interested in.
Hope this helps clear it up.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:
> Hi Ryan,
> Thank you for the response. Yes, I want to show both cost accounts if the
re
> is a tie. I am a little about the outer_table and derived_table.
> Supposed my table is called t1, how would the SQL be written? Thank you v
ery
> much.
> Roger
> "Ryan Powers" wrote:
>|||Got it! Thank you very much!!!
"Ryan Powers" wrote:
> Did you try to execute the SQL that either of us posted?
> It returns what you want.
> Anith gave 2 versions.
> The first is called a correlated subquery. But, it basically joining your
> table to a sql statement against the same table to figure out the max per
> Activity.
> The second (which is the same as what I posted) is called a derived table
or
> inline view. Basically, on the fly create a table that contains the activ
ity
> and its maximum hours from all rows for that activity. Then join this
> derived table with the regular table, to only find the rows that match the
> activity and the max hours found in the derived table.
> There is no way to do what you want with SQL with only the one table in th
e
> FROM. Instead you need join your table to some sql that gives you some
> specific information about your table in this case, you need to figure out
> the maximum hours for each activity so that you can figure out the correct
> rows to return from your table.
> My solution, and both of Anith's solution would work fine as the source of
a
> view to do exactly what you are trying to do.
> Hope this makes sense.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Yoyo" wrote:
>
I'm new to SQL programming, and I am having a hard time figuring this one ou
t.
I have a table containing the following information:
Activity Cost Account Hours
1 A 100
1 B 200
1 C 250
1 D 100
2 A 600
2 F 200
3 B 100
3 C 200
3 D 400
I would like to create a view that will show the Cost Account with the
highest hours per activity. For the table above, the end result would be
something like this.
Activity Cost Account
1 C
2 A
3 D
I hope this makes sense. Can someone please help me on how to write the SQL
statement for this? Thank you very much in advance!
RogerI'm not sure what you want to do in the case where 2 accounts have the same
number of hours (tied for the most). I'm going to assume you want two rows
returned.
SELECT outer_table.Activity, derived_table.[Cost Account]
From Unnamed_table as outer_table
INNER JOIN
(SELECT Activity, MAX(Hours) as Hours
FROM Unnamed_table
GROUP BY Activity) as derived_table
ON outer_table.Activity = derived_table.Activity
AND outer_table.Hours = derived_table.Hours
ORDER BY Activity
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:
> Hi all,
> I'm new to SQL programming, and I am having a hard time figuring this one
out.
> I have a table containing the following information:
> Activity Cost Account Hours
> 1 A 100
> 1 B 200
> 1 C 250
> 1 D 100
> 2 A 600
> 2 F 200
> 3 B 100
> 3 C 200
> 3 D 400
> I would like to create a view that will show the Cost Account with the
> highest hours per activity. For the table above, the end result would be
> something like this.
> Activity Cost Account
> 1 C
> 2 A
> 3 D
> I hope this makes sense. Can someone please help me on how to write the S
QL
> statement for this? Thank you very much in advance!
> Roger|||Do:
SELECT t1.activity, t1.Cost
FROM tbl t1
WHERE t1.hours = ( SELECT MAX( t2.hours )
FROM tbl t2
WHERE t2.Activity = t1.Activity )
ORDER BY t1.Activity ;
-- Or
SELECT t1.activity, t1.Cost
FROM tbl t1
INNER JOIN ( SELECT Activity, MAX( hours )
FROM tbl
GROUP BY Activity ) t2 ( Activity, hours )
ON t1.Activity = t2.Activity
AND t1.hours = t2.hours
ORDER BY t1.activity ;
Anith|||Hi Anith,
Thank you for the quick response. I am a little
about the SQLstatement. I only have one table and I want to create a view from that
table. But your SQL statement has 2 tables. Can you please clarify? I
think maybe most post wasn't clear. Thank you very much.
Roger
"Anith Sen" wrote:
> Do:
> SELECT t1.activity, t1.Cost
> FROM tbl t1
> WHERE t1.hours = ( SELECT MAX( t2.hours )
> FROM tbl t2
> WHERE t2.Activity = t1.Activity )
> ORDER BY t1.Activity ;
> -- Or
>
> SELECT t1.activity, t1.Cost
> FROM tbl t1
> INNER JOIN ( SELECT Activity, MAX( hours )
> FROM tbl
> GROUP BY Activity ) t2 ( Activity, hours )
> ON t1.Activity = t2.Activity
> AND t1.hours = t2.hours
> ORDER BY t1.activity ;
> --
> Anith
>
>|||Did you try to execute the SQL that either of us posted?
It returns what you want.
Anith gave 2 versions.
The first is called a correlated subquery. But, it basically joining your
table to a sql statement against the same table to figure out the max per
Activity.
The second (which is the same as what I posted) is called a derived table or
inline view. Basically, on the fly create a table that contains the activit
y
and its maximum hours from all rows for that activity. Then join this
derived table with the regular table, to only find the rows that match the
activity and the max hours found in the derived table.
There is no way to do what you want with SQL with only the one table in the
FROM. Instead you need join your table to some sql that gives you some
specific information about your table in this case, you need to figure out
the maximum hours for each activity so that you can figure out the correct
rows to return from your table.
My solution, and both of Anith's solution would work fine as the source of a
view to do exactly what you are trying to do.
Hope this makes sense.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:
> Hi Anith,
> Thank you for the quick response. I am a little
about the SQL> statement. I only have one table and I want to create a view from that
> table. But your SQL statement has 2 tables. Can you please clarify? I
> think maybe most post wasn't clear. Thank you very much.
> Roger
>
> "Anith Sen" wrote:
>|||Hi Ryan,
Thank you for the response. Yes, I want to show both cost accounts if there
is a tie. I am a little
about the outer_table and derived_table.Supposed my table is called t1, how would the SQL be written? Thank you ver
y
much.
Roger
"Ryan Powers" wrote:
> I'm not sure what you want to do in the case where 2 accounts have the sam
e
> number of hours (tied for the most). I'm going to assume you want two row
s
> returned.
> SELECT outer_table.Activity, derived_table.[Cost Account]
> From Unnamed_table as outer_table
> INNER JOIN
> (SELECT Activity, MAX(Hours) as Hours
> FROM Unnamed_table
> GROUP BY Activity) as derived_table
> ON outer_table.Activity = derived_table.Activity
> AND outer_table.Hours = derived_table.Hours
> ORDER BY Activity
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Yoyo" wrote:
>|||just replace unnamed_table with t1.
as in
SELECT outer_table.Activity, derived_table.[Cost Account]
From t1 as outer_table
INNER JOIN
(SELECT Activity, MAX(Hours) as Hours
FROM t1
GROUP BY Activity) as derived_table
ON outer_table.Activity = derived_table.Activity
AND outer_table.Hours = derived_table.Hours
ORDER BY Activity
Also, see my other post to you. I tried to explain the derived table and
Anith's correlated subquery to you. Neither of which is easy to explain in
a
couple sentences if you are new.
But, basically here is another attempt
Think of the table within the () as its own table completely separate from
your t1.
(SELECT Activity, MAX(Hours) as Hours
FROM t1
GROUP BY Activity)
This will give you each activity and the max hours for that activity. One
row per activity.
Now you join your table t1 to that new table (which I called derived_table)
on both activity and hours. This helps you identify the rows in t1 that you
are interested in.
Hope this helps clear it up.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:
> Hi Ryan,
> Thank you for the response. Yes, I want to show both cost accounts if the
re
> is a tie. I am a little
about the outer_table and derived_table.> Supposed my table is called t1, how would the SQL be written? Thank you v
ery
> much.
> Roger
> "Ryan Powers" wrote:
>|||Got it! Thank you very much!!!
"Ryan Powers" wrote:
> Did you try to execute the SQL that either of us posted?
> It returns what you want.
> Anith gave 2 versions.
> The first is called a correlated subquery. But, it basically joining your
> table to a sql statement against the same table to figure out the max per
> Activity.
> The second (which is the same as what I posted) is called a derived table
or
> inline view. Basically, on the fly create a table that contains the activ
ity
> and its maximum hours from all rows for that activity. Then join this
> derived table with the regular table, to only find the rows that match the
> activity and the max hours found in the derived table.
> There is no way to do what you want with SQL with only the one table in th
e
> FROM. Instead you need join your table to some sql that gives you some
> specific information about your table in this case, you need to figure out
> the maximum hours for each activity so that you can figure out the correct
> rows to return from your table.
> My solution, and both of Anith's solution would work fine as the source of
a
> view to do exactly what you are trying to do.
> Hope this makes sense.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Yoyo" wrote:
>
Friday, March 9, 2012
HELP! BACK button not working on matrix
I have a report that has a summary matrix containing all urls for a client
which will drill to a second report which is a detail matrix that shows
detail for the selected url from report #1. The problem is that when they
drill to report #2 ... if they expand the matrix to show individual detail
for past 30 days and then want to go back to the previous report and choose
another url ... the back button wont work unless they hit it twice. If they
happen to expand AND contract the detail and THEN want to go back to the prev
report they have to hit the browser back button THREE times. Report manager
or the browser seems to be keeping track of key strokes - however my user is
NOT HAPPY. How can I solve this? I need the equivalent of a BACK button.
But then ... do I have to put in all the parameters that the first report
required again? I am not sure what to do but I have to do it fast!Are you using the browser's back button or the report's back button? If
you're not using the browser's, try it. That should bring you back to
report #1.
You could also launch report two in a new window so that they could
just toggle between the two as they see fit, but I've had trouble
getting a report to launch in a new window.
Mike|||Mike,
Thanks for your reply. I am using the browser's back button (the report
doesnt have one that I can see that will take it to the previous report). I
have not had this problem with other reports ... just the matrix report and I
it has to do with the toggle to expand/contract detail. Launching another
window from the summary (first rpt) isnt allowed because this is deployed to
a portal and I am already launching a separate window there from an asp .net
page to pass report parameters to the first report. They dont want yet
*another* window to get the detail. Any other suggestions? I am not an asp
.net or vb .net wizard - someone else coded the initial UI that launches the
report so if you suggest something along those lines ... please be specific
with an example if possible. Thanks verry much!!
"Bassist695" wrote:
> Are you using the browser's back button or the report's back button? If
> you're not using the browser's, try it. That should bring you back to
> report #1.
> You could also launch report two in a new window so that they could
> just toggle between the two as they see fit, but I've had trouble
> getting a report to launch in a new window.
> Mike
>
which will drill to a second report which is a detail matrix that shows
detail for the selected url from report #1. The problem is that when they
drill to report #2 ... if they expand the matrix to show individual detail
for past 30 days and then want to go back to the previous report and choose
another url ... the back button wont work unless they hit it twice. If they
happen to expand AND contract the detail and THEN want to go back to the prev
report they have to hit the browser back button THREE times. Report manager
or the browser seems to be keeping track of key strokes - however my user is
NOT HAPPY. How can I solve this? I need the equivalent of a BACK button.
But then ... do I have to put in all the parameters that the first report
required again? I am not sure what to do but I have to do it fast!Are you using the browser's back button or the report's back button? If
you're not using the browser's, try it. That should bring you back to
report #1.
You could also launch report two in a new window so that they could
just toggle between the two as they see fit, but I've had trouble
getting a report to launch in a new window.
Mike|||Mike,
Thanks for your reply. I am using the browser's back button (the report
doesnt have one that I can see that will take it to the previous report). I
have not had this problem with other reports ... just the matrix report and I
it has to do with the toggle to expand/contract detail. Launching another
window from the summary (first rpt) isnt allowed because this is deployed to
a portal and I am already launching a separate window there from an asp .net
page to pass report parameters to the first report. They dont want yet
*another* window to get the detail. Any other suggestions? I am not an asp
.net or vb .net wizard - someone else coded the initial UI that launches the
report so if you suggest something along those lines ... please be specific
with an example if possible. Thanks verry much!!
"Bassist695" wrote:
> Are you using the browser's back button or the report's back button? If
> you're not using the browser's, try it. That should bring you back to
> report #1.
> You could also launch report two in a new window so that they could
> just toggle between the two as they see fit, but I've had trouble
> getting a report to launch in a new window.
> Mike
>
Friday, February 24, 2012
Help with XQuery
Hi all!
I have a SQL Server 2005 table with a XML field containing documents with
the following structure:
<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>
Via XQuery, it is possible to retrieve only the text that is contains within
a certain tag? For example, if I want the text that is surrounded by the "t"
tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in the same way,
the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT 3 TEXT 6".
Thanks in advance for the attentio.
--
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcomMarco Minerva wrote:
> I have a SQL Server 2005 table with a XML field containing documents
> with the following structure:
> <document>
> <title>Trittico di San Giovenale</title>
> <info>
> </info>
> <text>
> <ap>
> <e>
> <t>TEXT 1</t>
> TEXT 2
> <t>TEXT 3</t>
> </e>
> <t>TEXT 4</t>
> </ap>
> <ap>
> <t>TEXT 5
> <e>TEXT 6</e>
> </t>
> </ap>
> </text>
> </document>
> Via XQuery, it is possible to retrieve only the text that is contains
> within a certain tag? For example, if I want the text that is surrounded
> by the "t" tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in
> the same way, the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT
> 3 TEXT 6".
Using e.g. //e//text() you can access all descendant text nodes so
DECLARE @.x XML;
SET @.x = '<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>';
SELECT @.x.query('//e//text()') AS etext;
returns
TEXT 1
TEXT 2
TEXT 3TEXT 6
and
SELECT @.x.query('//t//text()') AS ttext;
returns
TEXT 1TEXT 3TEXT 4TEXT 5
TEXT 6
As you can see the query finds the right text nodes but includes white
space while you seem to want to strip white space but insert one space
between text nodes.
With XQuery 1.0 you could use the normalize-space and string-join
function but unfortunately the XQuery implementation in SQL server 2005
does not provide those functions.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OQpt96%23tHHA.4440@.TK2MSFTNGP06.phx.gbl...
> Marco Minerva wrote:
>
> Using e.g. //e//text() you can access all descendant text nodes so
>
> SELECT @.x.query('//e//text()') AS etext;
> returns
> TEXT 1
> TEXT 2
> TEXT 3TEXT 6
> and
> SELECT @.x.query('//t//text()') AS ttext;
> returns
> TEXT 1TEXT 3TEXT 4TEXT 5
> TEXT 6
>
> As you can see the query finds the right text nodes but includes white
> space while you seem to want to strip white space but insert one space
> between text nodes.
> With XQuery 1.0 you could use the normalize-space and string-join function
> but unfortunately the XQuery implementation in SQL server 2005 does not
> provide those functions.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
Hi!
Thank you very much, it is what I was looking for!
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom
I have a SQL Server 2005 table with a XML field containing documents with
the following structure:
<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>
Via XQuery, it is possible to retrieve only the text that is contains within
a certain tag? For example, if I want the text that is surrounded by the "t"
tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in the same way,
the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT 3 TEXT 6".
Thanks in advance for the attentio.
--
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcomMarco Minerva wrote:
> I have a SQL Server 2005 table with a XML field containing documents
> with the following structure:
> <document>
> <title>Trittico di San Giovenale</title>
> <info>
> </info>
> <text>
> <ap>
> <e>
> <t>TEXT 1</t>
> TEXT 2
> <t>TEXT 3</t>
> </e>
> <t>TEXT 4</t>
> </ap>
> <ap>
> <t>TEXT 5
> <e>TEXT 6</e>
> </t>
> </ap>
> </text>
> </document>
> Via XQuery, it is possible to retrieve only the text that is contains
> within a certain tag? For example, if I want the text that is surrounded
> by the "t" tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in
> the same way, the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT
> 3 TEXT 6".
Using e.g. //e//text() you can access all descendant text nodes so
DECLARE @.x XML;
SET @.x = '<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>';
SELECT @.x.query('//e//text()') AS etext;
returns
TEXT 1
TEXT 2
TEXT 3TEXT 6
and
SELECT @.x.query('//t//text()') AS ttext;
returns
TEXT 1TEXT 3TEXT 4TEXT 5
TEXT 6
As you can see the query finds the right text nodes but includes white
space while you seem to want to strip white space but insert one space
between text nodes.
With XQuery 1.0 you could use the normalize-space and string-join
function but unfortunately the XQuery implementation in SQL server 2005
does not provide those functions.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OQpt96%23tHHA.4440@.TK2MSFTNGP06.phx.gbl...
> Marco Minerva wrote:
>
> Using e.g. //e//text() you can access all descendant text nodes so
>
> SELECT @.x.query('//e//text()') AS etext;
> returns
> TEXT 1
> TEXT 2
> TEXT 3TEXT 6
> and
> SELECT @.x.query('//t//text()') AS ttext;
> returns
> TEXT 1TEXT 3TEXT 4TEXT 5
> TEXT 6
>
> As you can see the query finds the right text nodes but includes white
> space while you seem to want to strip white space but insert one space
> between text nodes.
> With XQuery 1.0 you could use the normalize-space and string-join function
> but unfortunately the XQuery implementation in SQL server 2005 does not
> provide those functions.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
Hi!
Thank you very much, it is what I was looking for!
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom
Help with XQuery
Hi all!
I have a SQL Server 2005 table with a XML field containing documents with
the following structure:
<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>
Via XQuery, it is possible to retrieve only the text that is contains within
a certain tag? For example, if I want the text that is surrounded by the "t"
tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in the same way,
the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT 3 TEXT 6".
Thanks in advance for the attentio.
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom
Marco Minerva wrote:
> I have a SQL Server 2005 table with a XML field containing documents
> with the following structure:
> <document>
> <title>Trittico di San Giovenale</title>
> <info>
> </info>
> <text>
> <ap>
> <e>
> <t>TEXT 1</t>
> TEXT 2
> <t>TEXT 3</t>
> </e>
> <t>TEXT 4</t>
> </ap>
> <ap>
> <t>TEXT 5
> <e>TEXT 6</e>
> </t>
> </ap>
> </text>
> </document>
> Via XQuery, it is possible to retrieve only the text that is contains
> within a certain tag? For example, if I want the text that is surrounded
> by the "t" tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in
> the same way, the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT
> 3 TEXT 6".
Using e.g. //e//text() you can access all descendant text nodes so
DECLARE @.x XML;
SET @.x = '<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>';
SELECT @.x.query('//e//text()') AS etext;
returns
TEXT 1
TEXT 2
TEXT 3TEXT 6
and
SELECT @.x.query('//t//text()') AS ttext;
returns
TEXT 1TEXT 3TEXT 4TEXT 5
TEXT 6
As you can see the query finds the right text nodes but includes white
space while you seem to want to strip white space but insert one space
between text nodes.
With XQuery 1.0 you could use the normalize-space and string-join
function but unfortunately the XQuery implementation in SQL server 2005
does not provide those functions.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OQpt96%23tHHA.4440@.TK2MSFTNGP06.phx.gbl...
> Marco Minerva wrote:
>
> Using e.g. //e//text() you can access all descendant text nodes so
>
> SELECT @.x.query('//e//text()') AS etext;
> returns
> TEXT 1
> TEXT 2
> TEXT 3TEXT 6
> and
> SELECT @.x.query('//t//text()') AS ttext;
> returns
> TEXT 1TEXT 3TEXT 4TEXT 5
> TEXT 6
>
> As you can see the query finds the right text nodes but includes white
> space while you seem to want to strip white space but insert one space
> between text nodes.
> With XQuery 1.0 you could use the normalize-space and string-join function
> but unfortunately the XQuery implementation in SQL server 2005 does not
> provide those functions.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
Hi!
Thank you very much, it is what I was looking for!
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom
I have a SQL Server 2005 table with a XML field containing documents with
the following structure:
<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>
Via XQuery, it is possible to retrieve only the text that is contains within
a certain tag? For example, if I want the text that is surrounded by the "t"
tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in the same way,
the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT 3 TEXT 6".
Thanks in advance for the attentio.
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom
Marco Minerva wrote:
> I have a SQL Server 2005 table with a XML field containing documents
> with the following structure:
> <document>
> <title>Trittico di San Giovenale</title>
> <info>
> </info>
> <text>
> <ap>
> <e>
> <t>TEXT 1</t>
> TEXT 2
> <t>TEXT 3</t>
> </e>
> <t>TEXT 4</t>
> </ap>
> <ap>
> <t>TEXT 5
> <e>TEXT 6</e>
> </t>
> </ap>
> </text>
> </document>
> Via XQuery, it is possible to retrieve only the text that is contains
> within a certain tag? For example, if I want the text that is surrounded
> by the "t" tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in
> the same way, the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT
> 3 TEXT 6".
Using e.g. //e//text() you can access all descendant text nodes so
DECLARE @.x XML;
SET @.x = '<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>';
SELECT @.x.query('//e//text()') AS etext;
returns
TEXT 1
TEXT 2
TEXT 3TEXT 6
and
SELECT @.x.query('//t//text()') AS ttext;
returns
TEXT 1TEXT 3TEXT 4TEXT 5
TEXT 6
As you can see the query finds the right text nodes but includes white
space while you seem to want to strip white space but insert one space
between text nodes.
With XQuery 1.0 you could use the normalize-space and string-join
function but unfortunately the XQuery implementation in SQL server 2005
does not provide those functions.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OQpt96%23tHHA.4440@.TK2MSFTNGP06.phx.gbl...
> Marco Minerva wrote:
>
> Using e.g. //e//text() you can access all descendant text nodes so
>
> SELECT @.x.query('//e//text()') AS etext;
> returns
> TEXT 1
> TEXT 2
> TEXT 3TEXT 6
> and
> SELECT @.x.query('//t//text()') AS ttext;
> returns
> TEXT 1TEXT 3TEXT 4TEXT 5
> TEXT 6
>
> As you can see the query finds the right text nodes but includes white
> space while you seem to want to strip white space but insert one space
> between text nodes.
> With XQuery 1.0 you could use the normalize-space and string-join function
> but unfortunately the XQuery implementation in SQL server 2005 does not
> provide those functions.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
Hi!
Thank you very much, it is what I was looking for!
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom
Help With Variable Containing Datetime
HI,
I HAVE A PROBLEM WITH A VARIABLE THAT I AM NOT BEEN ABLE TO SORT OUT.
DECLARE @.DATE NVARCHAR(100)
SET @.DATE = MONTH(GETDATE())
EXEC ('SELECT ' + @.DATE)
WHEN I RUN THIS, I HAVE NO PROBLEM AS IT GIVES ME THE ANSWER SAY 5 AS IT IS MAY.
BUT,
WHEN I RUN A VARIABLE CONTAINING DATETIME,
DECLARE @.DATE DATETIME
SET @.DATE = GETDATE()
EXEC ('SELECT ' + @.DATE)
IT GIVES ME AN ERROR :-
"Line 1: Incorrect syntax near '12'. "
IS THERE A WAY THAT I CAN USE DATETIME AS VARIABLE IN THIS CASE.Try this:
DECLARE @.DATE DATETIME
SET @.DATE = GETDATE()
EXEC ('SELECT ' + ''''+@.DATE+'''')
Harshal.|||Hi,
Thanks For Your Timely Help,the Problem Got Sorted Out In A Jiffy.|||And what about this approach:
declare @.date datetime
set @.date = getdate()
select @.date
Greetz,
DePrins
;)|||Hi,
Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004
Then I Have To Use The Exec Command.|||Hi,
Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004
Then I Have To Use The Exec Command.
Try this:
DECLARE @.DATE DATETIME,
@.Dt_Dsc Varchar(50),
@.SQL varchar(200)
SET @.DATE = GETDATE()
Set @.Dt_Ddc = Replace(Cast(Left(@.Date , 11) AS varchar(50)),' ','_')
Set @.SQL = 'Select ' + @.Dt_Dsc
Exec (@.SQL)
Gil|||In order to prevent you from tearing out your hair later, I'd like to strongly suggest that you format your dates differently and use them as a prefix rather than a suffix on your table names. If you format the prefix as E20040720 instead of 20_july2004, you won't have problems with 12_dec2000 sorting between 04_jul2004 and 20_may2010! If you use a prefix instead of a suffix, all of your extract tables will sort together by date of extract when you display table names sorted alphabetically. You can use:DECLARE @.prefix VARCHAR(10)
SET @.prefix = 'E' + Replace(Convert(VARCHAR(10), GetDate(), 120), '-', '')Note that I added a letter before the digits, just to make it easier to work with the tables going forward. Sometimes it gets messy trying to cope with table names that start with a digit.
-PatP|||Hi,
Thanks Pat And Glubstein That Was Wonderful Solved Many Of My Problems And Saved Many Headaches.
Thanks Once Again
I HAVE A PROBLEM WITH A VARIABLE THAT I AM NOT BEEN ABLE TO SORT OUT.
DECLARE @.DATE NVARCHAR(100)
SET @.DATE = MONTH(GETDATE())
EXEC ('SELECT ' + @.DATE)
WHEN I RUN THIS, I HAVE NO PROBLEM AS IT GIVES ME THE ANSWER SAY 5 AS IT IS MAY.
BUT,
WHEN I RUN A VARIABLE CONTAINING DATETIME,
DECLARE @.DATE DATETIME
SET @.DATE = GETDATE()
EXEC ('SELECT ' + @.DATE)
IT GIVES ME AN ERROR :-
"Line 1: Incorrect syntax near '12'. "
IS THERE A WAY THAT I CAN USE DATETIME AS VARIABLE IN THIS CASE.Try this:
DECLARE @.DATE DATETIME
SET @.DATE = GETDATE()
EXEC ('SELECT ' + ''''+@.DATE+'''')
Harshal.|||Hi,
Thanks For Your Timely Help,the Problem Got Sorted Out In A Jiffy.|||And what about this approach:
declare @.date datetime
set @.date = getdate()
select @.date
Greetz,
DePrins
;)|||Hi,
Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004
Then I Have To Use The Exec Command.|||Hi,
Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004
Then I Have To Use The Exec Command.
Try this:
DECLARE @.DATE DATETIME,
@.Dt_Dsc Varchar(50),
@.SQL varchar(200)
SET @.DATE = GETDATE()
Set @.Dt_Ddc = Replace(Cast(Left(@.Date , 11) AS varchar(50)),' ','_')
Set @.SQL = 'Select ' + @.Dt_Dsc
Exec (@.SQL)
Gil|||In order to prevent you from tearing out your hair later, I'd like to strongly suggest that you format your dates differently and use them as a prefix rather than a suffix on your table names. If you format the prefix as E20040720 instead of 20_july2004, you won't have problems with 12_dec2000 sorting between 04_jul2004 and 20_may2010! If you use a prefix instead of a suffix, all of your extract tables will sort together by date of extract when you display table names sorted alphabetically. You can use:DECLARE @.prefix VARCHAR(10)
SET @.prefix = 'E' + Replace(Convert(VARCHAR(10), GetDate(), 120), '-', '')Note that I added a letter before the digits, just to make it easier to work with the tables going forward. Sometimes it gets messy trying to cope with table names that start with a digit.
-PatP|||Hi,
Thanks Pat And Glubstein That Was Wonderful Solved Many Of My Problems And Saved Many Headaches.
Thanks Once Again
Subscribe to:
Posts (Atom)