XML Values to retrieve in single Column with Any(,) Delimiter

My Xml file

<Detials xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Tests>
  <Test Name="Test1" TotalMarks="100">95</Test>
  <Test Name="Test2" TotalMarks="200">65</Test>
  <Test Name="Test3" TotalMarks="150">95</Test>
  <Test Name="Test4" TotalMarks="150"></Test>
    </Tests>
</Detials>

I want to query it back to view as

  • Stored procedure conversion failed when converting the varchar value to data type int?
  • NOCHECK does not disable foreign key references
  • How to type a new line character in SQL Server Management Studio
  • Get the first letter of each word in a SQL string
  • How to display progress bar while executing big SQLCommand VB.Net
  • Write SqlDataReader to immediate window c#
  • TestName         |TotalMarks     |ScoreObtained** 
    Test1,Test2,Test3,Test4|100,200,150,150| 95,65,95,|
    

    I tried with this ,But not able to get the Result above as i want,I got the error as XQuery [T1.XMLData.query()]: “)” was expected.

    Select 
    
                               TestName  = x.c.query ('STUFF((SELECT 
                  '','' + fd.v.value(''(.)[1]'', ''varchar(100)'')
               FROM 
                  XMLwithOpenXML
               CROSS APPLY
                  XMLData.nodes(''/Tests/Test/@Name'') AS fd(v)
               FOR XML PATH('''')
              ), 1, 1, ''''') 
    
    
                            From T1 s
                            Cross Apply s.XMLData.nodes('/Detials') AS x(c)
    

    Help me to solve this !

    Thanks,Jayendran

  • GRANT syntax for domain\user
  • ASP.NET Website Administration Tool: Unable to connect to SQL Server database
  • Undelete recently deleted rows sql server
  • How to query hierarchical information with SQL-Server 2000?
  • Is there a way to get a list of all current temporary tables in SQL Server?
  • What is the meaning of “#” in front of a table name in TSQL?
  • 2 Solutions collect form web for “XML Values to retrieve in single Column with Any(,) Delimiter”

    SELECT STUFF(@x.query('for $a in (*:Detials/Tests/Test/@Name) return <a>{concat(",", $a)}</a>').value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS ListOfName
    SELECT STUFF(@x.query('for $a in (*:Detials/Tests/Test/@TotalMarks) return <a>{concat(",", $a)}</a>').value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS ListOfTotalMarks
    SELECT STUFF(@x.query('for $a in (*:Detials/Tests/Test) return <a>{concat(",", $a)}</a>').value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS ListOfInnerHTML
    

    Demo

    Note: first SELECT could be formatted like this:

    SELECT STUFF(
        @x.query('for $a in (*:Detials/Tests/Test/@Name) 
            return <a>{concat(",", $a)}</a>')
        .value('.', 'NVARCHAR(MAX)'), 
        1, 1, '') AS ListOfName
    

    ‘TestName = x.c.query (‘STUFF((SELECT …’

    You tried to put a ‘T-SQL’-statement within an XQuery-expression… That cannot work…

    This approach seems dangerous…

    With the given example the last entry is doubled. Your expected output shows three values as TestName, but four values as TotalMarksand four values (with an empty value at the end) as ScoreObtained. Your approach seems to me very dangerous… What, if there are two “Test1” entries? You would not know, whether the TotalMarks="200" belongs to this, or to “Test2”. Is this really the output you need?

    A possible solution

    You might try this, but I really doubt, that your approach is correct:

    DECLARE @xml XML=
    '<Detials xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Tests>
      <Test Name="Test1" TotalMarks="100">95</Test>
      <Test Name="Test2" TotalMarks="200">65</Test>
      <Test Name="Test3" TotalMarks="150">95</Test>
      <Test Name="Test3" TotalMarks="150"></Test>
        </Tests>
    </Detials>';
    
    WITH DerivedTable AS
    (
        SELECT t.value(N'@Name','nvarchar(max)') AS Name
              ,t.value(N'@TotalMarks','nvarchar(max)') AS Mark
              ,t.value(N'.','nvarchar(max)') AS Point --use 'text()[1]' to avoid empty values
        FROM @xml.nodes(N'/Detials/Tests/Test') AS A(t)
    )
    SELECT
        (
            STUFF(
            (
            SELECT ', ' + Name FROM DerivedTable GROUP BY Name ORDER BY Name FOR XML PATH(''),TYPE
            ).value(N'text()[1]','nvarchar(max)'),1,2,'')
        ) AS TestName
       ,(
            STUFF(
            (
            SELECT ', ' + Mark FROM DerivedTable ORDER BY Name FOR XML PATH(''),TYPE
            ).value(N'text()[1]','nvarchar(max)'),1,2,'')
        ) AS TotalMarks
       ,(
            STUFF(
            (
            SELECT ', ' + Point FROM DerivedTable ORDER BY Name FOR XML PATH(''),TYPE
            ).value(N'text()[1]','nvarchar(max)'),1,2,'')
        ) AS ScoreObtained;
    

    The result

    TestName            TotalMarks          ScoreObtained
    Test1, Test2, Test3 100, 200, 150, 150  95, 65, 95, 
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.