none
How to add a row count to a SELECT statment when using a XML Path? RRS feed

  • Question

  • I have a XML Path that I am using to concatenate a the fopermemo field. Before each of the fields being concatenated I want them to be counting (1. , 2. , 3. , 4. etc.). I've only been able to use hardcoded text and would like a running count.

    Select
    a.fquoteno,a.finumber,fgroup,fpartno,fpartrev,fdesc,fprodcl,fsource,
     Replace([Description], '&', '&') as [Description]
    From
    (
    Select 
    qtitem.fquoteno,qtitem.finumber,fgroup,fpartno,fpartrev,fdesc,fprodcl,fsource,
    ROW_NUMBER() OVER (PARTITION BY qtitem.fquoteno ORDER BY qtitem.finumber) AS RowNumber,
    
    STUFF
    ((
    --XML PATH
    select
     ' ' +  convert(varchar(max),fopermemo)+ CHAR(10)
    from qtdrtg 
    inner join qtitem on qtdrtg.fquoteno+qtdrtg.finumber = qtitem.fquoteno+qtitem.finumber
    where qtitem.fquoteno='003034' and qtitem.finumber='  1' 
    for XML Path ('')
    ---XML Path
    )
    ,1,1,'') as [Description]
     from qtdrtg 
    inner join qtitem on qtdrtg.fquoteno+qtdrtg.finumber = qtitem.fquoteno+qtitem.finumber
    where qtitem.fquoteno='003034' and qtitem.finumber='  1'
    ) a
    where RowNumber = 1

    Friday, February 15, 2019 9:19 PM

Answers

  • Sounds like this to me

    Select
    a.fquoteno,a.finumber,fgroup,fpartno,fpartrev,fdesc,fprodcl,fsource,
     Replace([Description], '&', '&') as [Description]
    From
    (
    Select 
    qtitem.fquoteno,qtitem.finumber,fgroup,fpartno,fpartrev,fdesc,fprodcl,fsource,
    ROW_NUMBER() OVER (PARTITION BY qtitem.fquoteno ORDER BY qtitem.finumber) AS RowNumber,
    
    STUFF
    ((
    --XML PATH
    select
     ' ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS varchar(5)) + '-' + convert(varchar(max),fopermemo)+ CHAR(10)
    from qtdrtg 
    inner join qtitem on qtdrtg.fquoteno+qtdrtg.finumber = qtitem.fquoteno+qtitem.finumber
    where qtitem.fquoteno='003034' and qtitem.finumber='  1' 
    for XML Path ('')
    ---XML Path
    )
    ,1,1,'') as [Description]
     from qtdrtg 
    inner join qtitem on qtdrtg.fquoteno+qtdrtg.finumber = qtitem.fquoteno+qtitem.finumber
    where qtitem.fquoteno='003034' and qtitem.finumber='  1'
    ) a
    where RowNumber = 1

    I've just counted based on random order

    If you want it based on any particular column then replace correct column name inside ROW_NUMBER expression instead of SELECT 1 part


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by David9501 Monday, February 18, 2019 6:24 PM
    Monday, February 18, 2019 5:51 AM

All replies

  • Can you show some sample data and your expected result? Thanks.
    Friday, February 15, 2019 9:42 PM
    Moderator
  • declare @t table (testtab varchar(25))

    insert @t (testtab) values ('supervalue')

    select  @@rowcount as '@id'
    ,       testtab
    from    @t
    for xml path('row'), root('rows')

    output-->

    <rows>
      <row id="1">
        <testtab>supervalue</testtab>
      </row>
    </rows>


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Friday, February 15, 2019 9:42 PM
  • I'm not sure that I understand what you are looking for, but may this example can be some guidance.

    WITH CTE AS (
        SELECT object_id, name, row_number() OVER(PARTITION BY object_id ORDER BY name) AS rowno
        FROM   sys.columns
    )
    SELECT o.name, c.list.value('.', 'nvarchar(MAX)')
    FROM   sys.objects o
    CROSS  APPLY (SELECT concat(rowno, ' ', name, char(13), char(10))
                  FROM   CTE
                  WHERE  o.object_id = CTE.object_id
                  ORDER  BY CTE.rowno
                  FOR XML PATH(''), TYPE) AS c(list)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, February 15, 2019 10:57 PM
  • Hi David9501,

    Thank you for your posting,

     

    Could you  please share us your table structure and some sample data along with your expected result? So that we’ll get a right direction and make some test.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 18, 2019 2:34 AM
  • Sounds like this to me

    Select
    a.fquoteno,a.finumber,fgroup,fpartno,fpartrev,fdesc,fprodcl,fsource,
     Replace([Description], '&amp;', '&') as [Description]
    From
    (
    Select 
    qtitem.fquoteno,qtitem.finumber,fgroup,fpartno,fpartrev,fdesc,fprodcl,fsource,
    ROW_NUMBER() OVER (PARTITION BY qtitem.fquoteno ORDER BY qtitem.finumber) AS RowNumber,
    
    STUFF
    ((
    --XML PATH
    select
     ' ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS varchar(5)) + '-' + convert(varchar(max),fopermemo)+ CHAR(10)
    from qtdrtg 
    inner join qtitem on qtdrtg.fquoteno+qtdrtg.finumber = qtitem.fquoteno+qtitem.finumber
    where qtitem.fquoteno='003034' and qtitem.finumber='  1' 
    for XML Path ('')
    ---XML Path
    )
    ,1,1,'') as [Description]
     from qtdrtg 
    inner join qtitem on qtdrtg.fquoteno+qtdrtg.finumber = qtitem.fquoteno+qtitem.finumber
    where qtitem.fquoteno='003034' and qtitem.finumber='  1'
    ) a
    where RowNumber = 1

    I've just counted based on random order

    If you want it based on any particular column then replace correct column name inside ROW_NUMBER expression instead of SELECT 1 part


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by David9501 Monday, February 18, 2019 6:24 PM
    Monday, February 18, 2019 5:51 AM