February 22, 2014 at 5:37 pm
I need to return multiple 'Start - End' dates in one row for company meetings.
Example: [Company Name] [Meeting Dates]
ALCOA 1/12/2014 - 1/13/2014;
2/10/2014 - 2/11/2014;
3/15/2014 - 3/16/2014;
Q. Is there an alternative method to using the 'For XML Path' below?
I currently use the FOR XML PATH with SUBSTRING or STUFF.
Select
SUBSTRING
((
SELECT
CONVERT(varchar(15),CAST(StartDate as TIME),100)
+ ' - ' +
CONVERT(varchar(15),CAST(EndDate AS TIME),100) + CHAR(10) as [text()]
FROM Table1 t2
WHERE (t2.id = t1.id) FOR XML path(''), elements
), 1, 100
)
as [Meeting Times]
From Table1 t1
February 23, 2014 at 12:42 pm
Yes, there are other options like using a cursor or loop but the FOR XML PATH one is simple and performs well.
We do not have a string concatenation ordered set function in T-SQL yet, like the function LISTAGG in Oracle. You can vote here is you think this is a feature that would like to have in T-SQL.
https://connect.microsoft.com/SQLServer/feedback/details/728969
February 23, 2014 at 8:11 pm
For one thing - it would be REALLY useful to see what kind f data structure you're starting from and what the XML output ought to be. That would really help give you some better answers.
There are lots of ways to give you what you wish and I am not convinced yet that FOR XML PATH isn't the way to do so.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 23, 2014 at 10:43 pm
I am providing 2 queries if it could help:-
SELECT CONVERT(VARCHAR(15), Cast(sdate AS DATE), 101) AS Startdate,
CONVERT(VARCHAR(15), Cast(edate AS DATE), 101) AS EndDate
FROM #table1
FOR xml raw, type, root('ALCOA');
SELECT CONVERT(VARCHAR(15), Cast(sdate AS DATE), 101) AS Startdate,
CONVERT(VARCHAR(15), Cast(edate AS DATE), 101) AS EndDate
FROM #table1
FOR xml path, type, root('ALCOA');
February 24, 2014 at 10:28 am
Currently the database contains '&' in the company name and displays as '&' in SQL Report Builder 3.0.
I can use a simple query and the '&' displays correctly: (Select location_name From Addresses)
I suspect the problem is the 'FOR XML PATH' formatting and SQL Report Builder 3.0 interpretation...
[Current Query Structure]
Select
Substring ((
Select a.location_name + char(10) + --(Chefs & Cooks Meeting Hall)
a.address1 + char(10) +
a.address2 + char(10) +
char(10) + char(10)
From Addresses a
Left Join Meetings_Addresses ma on a.id = ma.address_id
Where ma.meeting_id = m.id
For XML PATH(''), elements), 1, 200) as [Location_Address]
From Meetings m
[Goal]
Chefs & Cooks Meeting Hall
123 Main Street
Somewhere CA 12345
February 24, 2014 at 1:24 pm
If your XML PATH subquery uses ,TYPE to return an XML value, you can use .value() to restore the original text.
SELECT ( SELECT ...
FROM ...
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')
February 24, 2014 at 2:19 pm
Keep in mind that the XML standard prevents certain characters to appears without being "escaped". One of those is the & character.
If the issue is that the & doesn't "display correctly" - you're running against the XML standard itself. The
Your example would actually display as something like
<fun>Chefs & amp; Cooks Meeting Hall
</fun>
etc....
This is in accordance to the W3C desription laid out her (look at section 2.4 character markup)
http://www.w3.org/TR/REC-xml/[/url]
While you're there, you probably want to catch the section on white space/formatting (which might explain any potential loss in formatting as well).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 24, 2014 at 2:57 pm
Excellent! Thank you everyone...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply