Post reply

Pipe delimited row in tsql

  • June 20, 2012 at 8:26 am

    #1503472

    vinu512 (6/19/2012)

    Then what is the use of:

    root('MyString'), type).value('/MyString[1]','varchar(max)'

    You really need to understand how to parse the expression and it really helps to understand the Backus-Naur Form (BNF) descriptions in BOL. You can either work top-down or bottom up when parsing expressions. I'll use a top-down approach here.

    So Dwain's query is copied below.

    SELECT id, STUFF(

    (SELECT '|' + NAME

    FROM #temp1 t2

    WHERE t1.id = t2.id

    FOR XML PATH(''), root('MyString'), type).value('/MyString[1]','varchar(max)' )

    , 1, 1, '') As DelimitedString

    FROM #temp1 t1

    GROUP BY id

    Leaving out the optional parts from the SELECT this is

    SELECT select_list

    FROM table_source

    GROUP BY group_by_expression

    And from the SELECT Clause the select_list is equal to column_name, expression AS column_alias

    The expression consists of a STUFF function.

    STUFF( character_expression, start, length, replaceWith_expression)

    The start (1), length (1), and replaceWith_expression('') are all self-explanatory. So the only complex element is the character expression. This is where you misparsed the expression. This is an example of the value() Method.

    xml_column.value(XQuery, SQLType)

    The root and type are part of the subquery for the xml_column. You should know this, because of where the matching opening paren is located.

    You can find out more about the root and type directives at FOR XML clause.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply