FOR XML PATH Alternative?

  • 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

  • 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

  • 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?

  • 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');

  • 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

  • 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)')

  • 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?

  • 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