locked
How to remove XML tags from query result? RRS feed

  • Question

  • I am using this query to get multiple rows as a single row:

    DECLARE @str varchar(4000)
    SET @str = (SELECT * FROM customer FOR XML PATH(''))
    SET @str = SUBSTRING(@str,1,LEN(@str)-1)
    SELECT @str
    The output obtained contains XML tags which I want to remove. I want only column values.
    Saturday, May 4, 2013 1:37 PM

Answers

  • Hi OldEnthusiast,

    As far I as understand, what you want is actually concatenating string column values grouped by a common category column

    And you want to concatenate customer choice column values for each customer

    Please first refer to following SQL tutorial Comma seperated list using XML PATH() SQL Concatenation

    Then you can try the following Select script,

    create table Customer(ID smallint, Name varchar(100), Choice varchar(100))
    insert into Customer select 1,'John','BMW'
    insert into Customer select 1,'John','Toyota'
    insert into Customer select 2,'Jane','Porsche'
    SELECT 
      Distinct Id, Name,
      STUFF(
        (
          SELECT ',' + Choice
          FROM Customer
          WHERE Id = C.Id
          FOR XML PATH('')
        ), 1, 1, '') as Choices
    FROM Customer C

    I hope that helps for solution


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    Monday, May 6, 2013 6:44 AM

All replies

  • Specify the column names in the XML path query,

    create table customer (cid int identity(1,1),cname varchar(10))
    insert into customer values ('test1'),('test2')
    
    DECLARE @str varchar(4000)
    SET @str = (SELECT cname+',' FROM customer FOR XML PATH(''))
    SET @str = SUBSTRING(@str,1,LEN(@str)-1)
    SELECT @str


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, May 4, 2013 1:44 PM
  • But I don't want to mention a column name. I want to use:

    select *

    Saturday, May 4, 2013 1:46 PM
  • Hi,

    Is this what you need,like shown below example

    DECLARE @Employee TABLE
    (ID INT,
    Name VARCHAR(100),
    Age INT,
    Sex VARCHAR(50))
    INSERT @Employee SELECT 1,'Sathya',25,'Male'
    INSERT @Employee SELECT 2,'Sunny',24,'Female'
    DECLARE @xmldata XML
    SET @xmldata = (SELECT ID,Name,Age,Sex FROM @Employee FOR XML PATH ('Employee'))
    SELECT i.value('ID[1]','int') ID,
           i.value('Age[1]','int') Age,
    	   i.value('Name[1]','Varchar(20)') Name
    FROM @xmldata.nodes('/Employee') x(i)


    sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Saturday, May 4, 2013 1:47 PM
  • But I don't want to mention a column name. I want to use:

    select *

    can you post sample input and expected output?

    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, May 4, 2013 1:55 PM
  • I have a table with columns: ID, Name and Choice.

    A single customer can have multiple choices as:

    1, John, Porsche

    1,John,BMW

    I want to display result as:

    1, John, Porsche, BMW

    I want to use SELECT * FROM instead of specifying column names.

    Saturday, May 4, 2013 2:02 PM
  • I have a table with columns: ID, Name and Choice.

    A single customer can have multiple choices as:

    1, John, Porsche

    1,John,BMW

    I want to display result as:

    1, John, Porsche, BMW

    I want to use SELECT * FROM instead of specifying column names.

    Now its clear, try this ,

    create table customer (cid int,cname varchar(10) default 'test' ,cchoice varchar(10))
    insert into customer(cid,cchoice) values (1,'test1'),(1,'test2'),(2,'test3'),(3,'test4'),(3,'test5')
    
    
    select distinct cid,cname ,(SELECT cchoice+',' FROM customer C2
    where C2.cid=c1.cid FOR XML PATH('')) as choices from customer C1
    



    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Sarat (SS) Saturday, May 4, 2013 2:30 PM modified to select other columns as well
    Saturday, May 4, 2013 2:27 PM
  • Hi,

    Try like this example,

    DECLARE @csv TABLE (ID INT,
                        DBName VARCHAR(30))
    INSERT INTO @csv SELECT 1,'SQL Server 2005'
    INSERT INTO @csv SELECT 1,'SQL Server 2008'
    INSERT INTO @csv SELECT 1,'SQL Server 2008R2'
    INSERT INTO @csv SELECT 1,'SQL Server 2012'
    INSERT INTO @csv SELECT 2,'Oracle 10g'
    INSERT INTO @csv SELECT 2,'Oracle 11g'
    INSERT INTO @csv SELECT 2,'Oracle 12c'
    INSERT INTO @csv SELECT 3,'DB2 v9.7'
    INSERT INTO @csv SELECT 3,'DB2 v9.8'
      
      
    SELECT MAX(ID) ID, 
           STUFF((SELECT ' , ' + DBName
                  FROM   @csv X WHERE X.ID = Y.ID GROUP  BY ID,DBName
                  FOR XML PATH('')), 1, 2, '') CSV                
    FROM   @csv Y
    GROUP  BY ID


    sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Saturday, May 4, 2013 2:28 PM
  • Instead of specifying column names, why can I use simply:

    select *

    Saturday, May 4, 2013 2:36 PM
  • Instead of specifying column names, why can I use simply:

    select *

    Yes,in outer query, but it wont give u the desired result.

    You the need the choices in a single row, in that case how SQL knows which column to append , which one to ignore.

    Hope this makes something.


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, May 4, 2013 2:56 PM
  • not use select *

    http://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select

    Monday, May 6, 2013 5:31 AM
  • Hi OldEnthusiast,

    As far I as understand, what you want is actually concatenating string column values grouped by a common category column

    And you want to concatenate customer choice column values for each customer

    Please first refer to following SQL tutorial Comma seperated list using XML PATH() SQL Concatenation

    Then you can try the following Select script,

    create table Customer(ID smallint, Name varchar(100), Choice varchar(100))
    insert into Customer select 1,'John','BMW'
    insert into Customer select 1,'John','Toyota'
    insert into Customer select 2,'Jane','Porsche'
    SELECT 
      Distinct Id, Name,
      STUFF(
        (
          SELECT ',' + Choice
          FROM Customer
          WHERE Id = C.Id
          FOR XML PATH('')
        ), 1, 1, '') as Choices
    FROM Customer C

    I hope that helps for solution


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    Monday, May 6, 2013 6:44 AM
  • Hello, the best way is this:  
     (SELECT * FROM customer FOR XML PATH(''), type ).value('.', 'nvarchar(max)')
      
    Friday, November 4, 2016 8:25 PM