Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

FOR XML PATH Alternative? Expand / Collapse
Author
Message
Posted Saturday, February 22, 2014 5:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 24, 2014 2:57 PM
Points: 9, Visits: 21
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
Post #1544269
Posted Sunday, February 23, 2014 12:42 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 12:25 PM
Points: 99, Visits: 496
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




Post #1544305
Posted Sunday, February 23, 2014 8:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 7,084, Visits: 14,679
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?
Post #1544341
Posted Sunday, February 23, 2014 10:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:11 AM
Points: 37, Visits: 139
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');
Post #1544349
Posted Monday, February 24, 2014 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 24, 2014 2:57 PM
Points: 9, Visits: 21
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
Post #1544638
Posted Monday, February 24, 2014 1:24 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:24 PM
Points: 2,835, Visits: 1,124
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)')




Post #1544698
Posted Monday, February 24, 2014 2:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 7,084, Visits: 14,679
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/

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?
Post #1544715
Posted Monday, February 24, 2014 2:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 24, 2014 2:57 PM
Points: 9, Visits: 21
Excellent! Thank you everyone...
Post #1544722
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse