none
For XML Root element

    Question

  •  

    HI,

    Can we insert an attribute in the root element set through for xml

     

    Code Snippet

     

    SELECT * FROM customer FOR XML Raw, ELEMENTS XSINIL, ROOT('customers RecCount="4"')

    --Required Out Put

     

    <customers RecCount="4">
      <customer>
        <cid>1</cid>
        <cname>Name1</cname>
        <cadd>Address1</cadd>
        <ctel>Tel1</ctel>
      </customer>
      <customer>
        <cid>2</cid>
        <cname>Name2</cname>
        <cadd>Address2</cadd>
        <ctel>Tel2</ctel>
      </customer>
      <customer>
        <cid>3</cid>
        <cname>Name3</cname>
        <cadd>Address3</cadd>
      </customer>
      <customer>
        <cid>4</cid>
        <cname>Name4</cname>
      </customer>
    </customers>

     

     

    Tuesday, September 30, 2008 8:41 AM

Answers

  • Which version of SQL Server are you working with?

    If it is 2005, the following will return the required result.

     

    Code Snippet

    DECLARE @t TABLE (CustomerID INT, Name VARCHAR(20), Address VARCHAR(20), Tel VARCHAR(20))

    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 1, 'Name1', 'Address1', 'Tel1'

    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 2, 'Name2', 'Address2', 'Tel2'

    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 3, 'Name3', 'Address3', 'Tel3'

    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 4, 'Name4', 'Address4', 'Tel4'

     

    SELECT

    4 AS '@RecCount',

    (

    SELECT

    CustomerID AS cid,

    Name AS cname,

    Address AS cadd,

    Tel AS ctel

    FROM @t

    FOR XML PATH('customer'), TYPE

    )

    FOR XML PATH('Customers')

     

     

    Tuesday, September 30, 2008 9:40 AM

All replies

  • Which version of SQL Server are you working with?

    If it is 2005, the following will return the required result.

     

    Code Snippet

    DECLARE @t TABLE (CustomerID INT, Name VARCHAR(20), Address VARCHAR(20), Tel VARCHAR(20))

    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 1, 'Name1', 'Address1', 'Tel1'

    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 2, 'Name2', 'Address2', 'Tel2'

    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 3, 'Name3', 'Address3', 'Tel3'

    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 4, 'Name4', 'Address4', 'Tel4'

     

    SELECT

    4 AS '@RecCount',

    (

    SELECT

    CustomerID AS cid,

    Name AS cname,

    Address AS cadd,

    Tel AS ctel

    FROM @t

    FOR XML PATH('customer'), TYPE

    )

    FOR XML PATH('Customers')

     

     

    Tuesday, September 30, 2008 9:40 AM
  • Hello Jocob,

    How can I add the namespace to the root element Customers and still keep RecCount="4" in the root element ?

    Like
    < customers RecCount="4" xmlns:h="http://www.w3.org/TR/html4/" >

    Thanks!
    Saturday, July 25, 2009 4:06 AM
  • Hello hello

    Try
    DECLARE @t TABLE (CustomerID INT, Name VARCHAR(20), Address VARCHAR(20), Tel VARCHAR(20));
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 1, 'Name1', 'Address1', 'Tel1';
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 2, 'Name2', 'Address2', 'Tel2';
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 3, 'Name3', 'Address3', 'Tel3';
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 4, 'Name4', 'Address4', 'Tel4';


    WITH
    XMLNAMESPACES ('http://www.w3.org/TR/html4/' as h) SELECT 4 AS <a href="mailto:'@RecCount'">'@RecCount' , (SELECT CustomerID AS cid , Name AS cname , Address AS cadd , Tel AS ctel FROM @t FOR XML PATH('customer'), TYPE) FOR XML PATH('Customers');

    Best regards

     

     

    Michael
    • Proposed as answer by M. Rys [MSFT] Saturday, July 25, 2009 6:45 PM
    • Proposed as answer by M. Rys [MSFT] Saturday, July 25, 2009 6:45 PM
    Saturday, July 25, 2009 6:42 PM
  • Hello Michael,

    <a href="mailto:'@RecCount'"
    > causes the problem, so I remove it.
    But I got the xml like this, namespace appears many times:

    <Customers xmlns:h="http://www.w3.org/TR/html4/" RecCount="4">
      <customer xmlns:h="http://www.w3.org/TR/html4/">
        <cid>1</cid>
        <cname>Name1</cname>
        <cadd>Address1</cadd>
        <ctel>Tel1</ctel>
      </customer>
      <customer xmlns:h="http://www.w3.org/TR/html4/">
        <cid>2</cid>
        <cname>Name2</cname>
        <cadd>Address2</cadd>
        <ctel>Tel2</ctel>
      </customer>
      <customer xmlns:h="http://www.w3.org/TR/html4/">
        <cid>3</cid>
        <cname>Name3</cname>
        <cadd>Address3</cadd>
        <ctel>Tel3</ctel>
      </customer>
      <customer xmlns:h="http://www.w3.org/TR/html4/">
        <cid>4</cid>
        <cname>Name4</cname>
        <cadd>Address4</cadd>
        <ctel>Tel4</ctel>
      </customer>
    </Customers>
    Sunday, July 26, 2009 1:53 PM
  • This is a feature of WITH XMLNAMESPACES.  It shouldn't cause a problem for the XML or anything reading it, but it can bloat the XML a bit.  You can workaround by creating the sections of XML separately, eg

    DECLARE @t TABLE (CustomerID INT, Name VARCHAR(20), Address VARCHAR(20), Tel VARCHAR(20));
    
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 1, 'Name1', 'Address1', 'Tel1';
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 2, 'Name2', 'Address2', 'Tel2';
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 3, 'Name3', 'Address3', 'Tel3';
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 4, 'Name4', 'Address4', 'Tel4'
    
    DECLARE @xml XML
    
    SELECT @xml = (SELECT CustomerID AS cid
                 , Name AS cname
                 , Address AS cadd
                 , Tel AS ctel
            FROM @t
            FOR XML PATH('customer'), TYPE)
            
    ;WITH XMLNAMESPACES ('http://www.w3.org/TR/html4/' as h)
    SELECT 4 AS '@RecCount'
         , @xml
    FOR XML PATH('Customers');
    There is a connect ( feature request ) to add greater control to namespace place with XMLNAMESPACES which you should go and vote for here:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265956&wa=wsignin1.0


    Monday, July 27, 2009 9:09 AM
  • Hi wBob, thanks for the suggestion and it works. But when I change
    SELECT
     @xml = (SELECT
     CustomerID AS
     cid
    to

    SELECT
     @xml = (SELECT
     CustomerID AS
     "h:cid"
    I got error
    XML name space prefix 'h' declaration is missing for FOR XML column name 'h:cid'.
    How can I create the following XML that really has the namespace ?

    <h:Customers xmlns:h="http://www.w3.org/TR/html4/" RecCount="4">
      <h:customer>
        <h:cid>1</h:cid>
        <h:cname>Name1</h:cname>
        <h:cadd>Address1</h:cadd>
        <h:ctel>Tel1</h:ctel>
      </h:customer>
    ...
    </h:hCustomers>
    Monday, July 27, 2009 2:10 PM
  • Sorry, bit of confusion there.  The only way I could get this to work was using the modify method of the xml data-type.

    DECLARE @t TABLE (CustomerID INT, Name VARCHAR(20), Address VARCHAR(20), Tel VARCHAR(20));
    
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 1, 'Name1', 'Address1', 'Tel1';
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 2, 'Name2', 'Address2', 'Tel2';
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 3, 'Name3', 'Address3', 'Tel3';
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 4, 'Name4', 'Address4', 'Tel4';
    
    DECLARE @xml XML
    
    ;WITH XMLNAMESPACES ( 'http://www.w3.org/TR/html4/' AS h )
    SELECT @xml = 
    	(
    	SELECT CustomerID AS "h:cid", Name AS "h:cname", Address AS "h:cadd", Tel AS "h:ctel"
    	FROM @t
    	FOR XML PATH('h:customer'), ROOT( 'h:Customers' ), TYPE
    	)
    
    SET @xml.modify('declare namespace h = "http://www.w3.org/TR/html4/"; insert attribute RecCount{4} as last into (h:Customers)[1]')
    
    SELECT @xml

    You should know that having the namespace in multiple places in the xml doesn't do it any harm except bloating it a bit, so only use the above method if you have to.
    Monday, July 27, 2009 4:21 PM
  • Thanks  a lot, wBob.  What if
    RecCount{4}
    
    is not harded coded. For example, it's the row count. I have
    DECLARE @t TABLE (CustomerID INT, Name VARCHAR(20), Address VARCHAR(20), Tel VARCHAR(20));

    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 1, 'Name1', 'Address1', 'Tel1';
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 2, 'Name2', 'Address2', 'Tel2';
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 3, 'Name3', 'Address3', 'Tel3';
    INSERT INTO @t (CustomerID, Name, Address, Tel) SELECT 4, 'Name4', 'Address4', 'Tel4'

    DECLARE @xml XML,
    @RecCount int,
    @RecCountStr varchar(10);

    select @RecCount =  count(*) from @t

    select @RecCountStr = CONVERT(char(10), @RecCount)

    ;WITH XMLNAMESPACES ( 'http://www.w3.org/TR/html4/' AS h )
    SELECT @xml =
        (
        SELECT CustomerID AS "h:cid", Name AS "h:cname", Address AS "h:cadd", Tel AS "h:ctel"
        FROM @t
        FOR XML PATH('h:customer'), ROOT( 'h:Customers' ), TYPE
        )

    SET @xml.modify('declare namespace h = "http://www.w3.org/TR/html4/"; insert attribute ' + @RecCountStr +' as last into (h:Customers)[1]')

    SELECT @xml

    I got error ''The argument 1 of the xml data type method "modify" must be a string literal."  on
    SET @xml.modify('declare namespace h = "http://www.w3.org/TR/html4/"; insert attribute ' + @RecCountStr +' as last into (h:Customers)[1]')

    how can I pass in String variable @RecCountStr ?



    Tuesday, July 28, 2009 2:17 AM
  • I get it:
    sql:variable("@RecCount")
    Tuesday, July 28, 2009 3:06 AM