Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 922
  • Last Modified:

For XML Path Alternative Method?

Is there an alternative method to using the 'For XML Path' below?

Select  
SUBSTRING 
(
(
SELECT 
CONVERT(varchar(15),CAST(StartDate as TIME),100)
 + ' - ' + 
CONVERT(varchar(15),CAST(EndDate AS TIME),100)  + CHAR(10) as [text()]
FROM Table2 MD
WHERE (MD.id = Table1.id) FOR XML path(''), elements
), 1, 100
) 
as [Meeting Times]

From Table1

Open in new window

0
WorknHardr
Asked:
WorknHardr
  • 7
  • 4
1 Solution
 
käµfm³d 👽Commented:
What are you trying to accomplish?
0
 
WorknHardrAuthor Commented:
The result is more clearly seen in SQL Report Builder 3.0 like so. I'm building MSSQL SP to load into the report dataset.

[Meeting Times]
01/01/2014 - 01/03/2014
02/07/2014 - 02/09/2014
03/14/2014 - 03/17/2014


Select  
SUBSTRING 
(
(
SELECT 
CONVERT(varchar(15),CAST(StartDate as TIME),100)
 + ' - ' + 
CONVERT(varchar(15),CAST(EndDate AS TIME),100)  + CHAR(10) as [text()]
FROM Table2 MD
WHERE (MD.id = Table1.id) FOR XML path(''), elements
), 1, 100
) 
as [Meeting Times]

From Table1

Open in new window

0
 
Anthony PerkinsCommented:
There are a number of methods you can use.  For example you can use a scalar UDF to do this.  Let me know if you can use functions and if you need an example.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
WorknHardrAuthor Commented:
I like reusable functions.
0
 
Anthony PerkinsCommented:
Please post the schema for your Table1 and Table2 and I will see what I can do.
0
 
WorknHardrAuthor Commented:
[Table1]
ID1    Name
1        Meeting Times

[Table2]
ID2    ID1   StartDate         EndDate
 1         1     01/01/2014     01/03/2014
 2         1     02/07/2014     02/09/2014
 3         1     03/14/2014     03/17/2014

[Meeting Times]
01/01/2014 - 01/03/2014
02/07/2014 - 02/09/2014
03/14/2014 - 03/17/2014

Open in new window

0
 
Anthony PerkinsCommented:
So the schema for
Table 1
ID integer.
Name varchar(20)

Table 2
ID2 integer,
ID1 integer
StartDate datetime,
EndDate datetime

And they are both related by ID1.  Is that correct?
0
 
WorknHardrAuthor Commented:
Yes, indeed...
0
 
Anthony PerkinsCommented:
Perhaps we have made it more complicated than it needs to be.  Why not just write the query like this:
SELECT	CONVERT(varchar(10), StartDate, 101) + '-' + CONVERT(varchar(10), EndDate, 101) [Meeting Times]
FROM	Table2

Open in new window


What am I missing?
0
 
WorknHardrAuthor Commented:
You got it.
I think the next question is:
Q. Why use 'FOR XML' in the first place?
0
 
WorknHardrAuthor Commented:
thx
0
 
WorknHardrAuthor Commented:
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now