locked
taking values from xml build. RRS feed

  • Question

  • Hi,

    I am having below xml tag from which i need to take only values by contacting with "#" b/w values. I need this through sqlserver 2008. Can any help in this?

    Note: this xml build tag is in one of the column in table.

    Xml: 

    <Q13>
          <itemscollection>
            <item>
              <name>Name (First &amp;amp; Last)</name>
              <fieldName>Name (First &amp;amp; Last)</fieldName>
              <value>Test Name</value>
            </item>
            <item>
              <name>Phone# (with Area Code)</name>
              <fieldName>Phone# (with Area Code)</fieldName>
              <value>123-456-7890</value>
            </item>
            <item>
              <name>Email Address</name>
              <fieldName>Email Address</fieldName>
              <value>test@test.com</value>
            </item>
          </itemscollection>
        </Q13>

    Required format from above build xml:

    <Q13>Test Name#123-456-7890#test@test.com</Q13>


    Vijay

    Thursday, October 18, 2012 2:13 PM

Answers

  • This is a slightly odd format.  Why do you need multiple values in one element, separated by #?

    Anyway, try this:

     DECLARE @yourTable TABLE ( rowId INT IDENTITY PRIMARY KEY, yourXML XML )
    
     INSERT INTO @yourTable 
     SELECT '<Q13>
           <itemscollection>
             <item>
               <name>Name (First &amp;amp; Last)</name>
               <fieldName>Name (First &amp;amp; Last)</fieldName>
               <value>Test Name</value>
             </item>
             <item>
               <name>Phone# (with Area Code)</name>
               <fieldName>Phone# (with Area Code)</fieldName>
               <value>123-456-7890</value>
             </item>
             <item>
               <name>Email Address</name>
               <fieldName>Email Address</fieldName>
               <value>test@test.com</value>
             </item>
           </itemscollection>
         </Q13>'
     
    
    SELECT rowId, yourXML.query('for $v in //item/value return concat(data($v), "#")').query('<Q13>{.}</Q13>')
    FROM @yourTable t


    Or use this one to trim trailing #:

    -- Trim trailing #
    SELECT rowId, yourXML.query('for $v in //item/value return 
    concat(data($v), "#")').query('<Q13>{substring(.,1,string-length(.)-1)}</Q13>')
    FROM @yourTable t

    • Edited by wBobEditor Thursday, October 18, 2012 3:04 PM trailing #
    • Marked as answer by Iric Wen Friday, October 26, 2012 8:08 AM
    Thursday, October 18, 2012 2:47 PM
    Answerer

All replies

  • This is a slightly odd format.  Why do you need multiple values in one element, separated by #?

    Anyway, try this:

     DECLARE @yourTable TABLE ( rowId INT IDENTITY PRIMARY KEY, yourXML XML )
    
     INSERT INTO @yourTable 
     SELECT '<Q13>
           <itemscollection>
             <item>
               <name>Name (First &amp;amp; Last)</name>
               <fieldName>Name (First &amp;amp; Last)</fieldName>
               <value>Test Name</value>
             </item>
             <item>
               <name>Phone# (with Area Code)</name>
               <fieldName>Phone# (with Area Code)</fieldName>
               <value>123-456-7890</value>
             </item>
             <item>
               <name>Email Address</name>
               <fieldName>Email Address</fieldName>
               <value>test@test.com</value>
             </item>
           </itemscollection>
         </Q13>'
     
    
    SELECT rowId, yourXML.query('for $v in //item/value return concat(data($v), "#")').query('<Q13>{.}</Q13>')
    FROM @yourTable t


    Or use this one to trim trailing #:

    -- Trim trailing #
    SELECT rowId, yourXML.query('for $v in //item/value return 
    concat(data($v), "#")').query('<Q13>{substring(.,1,string-length(.)-1)}</Q13>')
    FROM @yourTable t

    • Edited by wBobEditor Thursday, October 18, 2012 3:04 PM trailing #
    • Marked as answer by Iric Wen Friday, October 26, 2012 8:08 AM
    Thursday, October 18, 2012 2:47 PM
    Answerer
  • DECLARE @XML XML = '<Q13>
           <itemscollection>
             <item>
               <name>Name (First &amp;amp; Last)</name>
               <fieldName>Name (First &amp;amp; Last)</fieldName>
               <value>Test Name</value>
             </item>
             <item>
               <name>Phone# (with Area Code)</name>
               <fieldName>Phone# (with Area Code)</fieldName>
               <value>123-456-7890</value>
             </item>
             <item>
               <name>Email Address</name>
               <fieldName>Email Address</fieldName>
               <value>test@test.com</value>
             </item>
           </itemscollection>
         </Q13>'
         SELECT '<Q13>' + REPLACE(@XML.query('data(//value)').value('.','varchar(100)'),' ','#') + '</Q13>'
    


    Thanks & Regards, sathya

    Friday, October 19, 2012 11:33 AM