Hi there: We have xml data stored in a table column and need to pull them out and have displayed on a report. I understand it possible to parse HTML markup by setting the placeholder properties. Any idea on how to parse XML contents? thanks Hui--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

You can shred the XML in a stored proc using OPENXML for SQL 2000 and up, or if 2005 up use the XML data type. If you can get an XML datasource type then in query designer this is the query to parse everything : <Query/>

thanks gao. Can you elaborate a bit? I am afraid I can't follow you. Step 1: Use a SP to shred the XML? what do you mean by this? Step 2: Use the query to parse everything: <Query/> , I am afraid I did not find the query in your posting. Cheers Hui--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

DECLARE @x xml ; SET @x='<Root> <row id="1"><name>Larry</name><oflw>some text</oflw></row> <row id="2"><name>moe</name></row> <row id="3" /> </Root>'; SELECT T.c.value('@id','varchar(100)') AS id , n.value('.','varchar(100)') as name --SELECT T.c.value('/oflw','varchar(100)') AS oflw FROM @x.nodes('/Root/row') T(c) cross apply t.c.nodes('./name') n(n) GO <Query/> is the query, you know - like who's on first? For an XML data source that will parse it into a dataset. This is a basic sql xml shredder

Hi gao: I think there is a confusion. I am not looking to parse an XML datasource. We have xml data stored in a table column and need to pull them out and have displayed on a report. thanks for your help anyway Hui--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

you can shred the xml using xpath as I showed you above, shred it in the dataset query, or just display it raw like any other string. I am working on similar problem, I don't want shred it, I want to display the xml but formatted properly.

Have you any luck on this? I got pretty far using html placeholder and code that uses xslt. But I am not getting indentation working, and few other bugs. This is my code to put in the report, also need reference to system.xml Public Function IndentXMLString2(ByVal pxml As String) As String Dim document As System.Xml.XmlDocument ' Xml document root Dim navigator As System.Xml.XPath.XPathNavigator ' navigate document Dim transformer As System.Xml.Xsl.XslCompiledTransform ' transform document Dim output As System.IO.StringWriter Dim reader As System.Xml.XmlTextReader Dim sr As System.IO.StringReader Dim xmlNodeReader As System.Xml.XmlNodeReader Dim strXSLT As String = "<?xml version='1.0' encoding='iso-8859-1'?>" & _ "<xsl:stylesheet version=|1.0| xmlns:xsl=|http://www.w3.org/1999/XSL/Transform|>" & _ "<xsl:output method=|xml| omit-xml-declaration=|yes| indent=|yes|/>" & _ "<xsl:template match=|*|>" & _ "<span style='color:red'>" & _ " <xsl:text>&lt;</xsl:text>" & _ " <xsl:value-of select=|name()|/>" & _ "</span>" & _ " <xsl:for-each select=|@*|>" & _ " <xsl:text> </xsl:text>" & _ "<span style='color:blue'>" & _ " <xsl:value-of select='name()'/>" & _ "</span>" & _ " <xsl:text>=|</xsl:text>" & _ " <xsl:value-of select='.'/>" & _ " <xsl:text>|</xsl:text>" & _ " </xsl:for-each>" & _ " <xsl:text>&gt;</xsl:text>" & _ " <xsl:apply-templates/>" & _ "<span style='color:red'>" & _ " <xsl:text>&lt;/</xsl:text>" & _ " <xsl:value-of select=|name()|/>" & _ " <xsl:text>&gt;</xsl:text>" & _ "</span>" & _ "</xsl:template>" & _ "</xsl:stylesheet>" Dim newstring As String = strXSLT.Replace("|", Chr(34)).ToString() document = New System.Xml.XmlDocument() document.LoadXml(pxml) navigator = document.CreateNavigator xmlNodeReader = New System.Xml.XmlNodeReader(document) transformer = New System.Xml.Xsl.XslCompiledTransform() sr = New System.IO.StringReader(newstring) reader = New System.Xml.XmlTextReader(sr) transformer.Load(reader) output = New System.IO.StringWriter() transformer.Transform(xmlNodeReader, Nothing, output) Dim myass As String = output.ToString() output.Close() myass = myass.Replace("&gt;", "&gt;<br>&nbsp;") Return myass End Function

thanks gao. I manage to parse the XML in my report. --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

Hi @gao or anyone, I have some data in an XML webresource whose url is passed in as a report parameter I can hard-code url in the XML datasource and everything works fine, but as soon as I try to use a dynamic url from report parameter it fails. Now, I know that you can use dynamic connectionstring for SQL, but it seems you can't for XML connectionstring - can you confirm this? This report gets deployed on different servers and changing the XML connectionstring in each rdl prior to deployment is a pain... Any possible workaround? Thanks. Chris

hi cat_ca, Do you have any tips on the approach that you used to parse the xml? I could parse the xml on the stored procedure and returns column, but I would rather do the processing on the report instead of the database. thanks.0g

hi 0zkitar, have you tried embedded code or assembly?--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

I have found that in order to instantiate these types in the report code you need to change the config to support partial trust which was not an option for me so I had to select the column, decode base64 and shred it into a standard table recordset in the SQL query.