XML file conversion to text

• Adrian Nichols-360275

  SSCertifiable

  Points: 7694

  More actions

  July 31, 2007 at 10:08 am

  #723647

  What's the final outcome that you are trying to achieve? I can't understand why you'd need to do this on a stylesheet?

  It's a tricky one with xsl. You can't have a colon ( : ) in the tag name when using OPENXML so the first thing to do is replace the colons with an allowable character (for example '_').

  You can then start to use OPENXML to return attribute and element details.

  DECLARE @idoc int

  DECLARE @doc varchar(2000)

  SET @doc ='

  <?xml version="1.0"?>

  <xsl:stylesheet version = "1.0"

      xmlns:xsl = "http://www.w3.org/1999/XSL/Transform">

   <xsl:output method="text"/>

  <xsl:template match="stateTotal">

    <xsl:value-of select="normalize-space(./state)"/><xsl:text>;</xsl:text>

    <xsl:value-of select="normalize-space(./cTotal)"/><xsl:text>;</xsl:text>

    <xsl:value-of select="normalize-space(./eTotal)"/><xsl:text>;</xsl:text>

    <xsl:value-of select="normalize-space(./Total)"/><xsl:text>;</xsl:text>

   

   </xsl:template>

  </xsl:stylesheet>

  '

  SELECT @doc = REPLACE(@doc, ':', '_')

  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

  SELECT colName

  FROM   OPENXML (@idoc, '/xsl_stylesheet/xsl_template/xsl_value-of', 1)

         WITH ( colName  varchar(100) '@select')

  ---

  
  Ade

  A Freudian Slip is when you say one thing and mean your mother.
  For detail-enriched answers, ask detail-enriched questions...[/url]