Using RS2000
I have a field in a SQL DB which contains data stored as an XML string. Is there an easy way to parse this string into data without moving to SS2005?
I have builts a parser in SQL, but I would like to know if RS had a way to directly read this. If not, is there a method in RS2005 which handles this type of data?
The only way to really do this in RS2000 is to create your own custom data processing extension that encapsulates the code you have written to parse the Xml. There are quite a few examples online.In RS2005, you can use the Xml Data Processing Extension, which is designed for retieving data from xml data sources. It retrieves the data from an xml document which is retireved from a web service, web page, or can use a document embedded in the Xml Query. This extension is very powerful, but can also be somewhat complex for advanced queries.
For more information: http://msdn2.microsoft.com/en-us/library/ms159741.aspx|||
you can write a function in report Code section that will take the xml string, load it into System.Xml.XmlDocument and parse it.
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>server=.</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>6cfe005e-c5e0-4297-aa6b-f2ca0673ec05</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<List Name="list1">
<Left>1.25in</Left>
<ReportItems>
<Textbox Name="my_xml_1">
<Left>0.5in</Left>
<Top>0.5in</Top>
<rd:DefaultName>my_xml_1</rd:DefaultName>
<Width>3.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Code.ParseXml(Fields!my_xml.Value)</Value>
</Textbox>
</ReportItems>
<DataSetName>DataSet1</DataSetName>
<Top>0.375in</Top>
<Height>1in</Height>
</List>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>c9ad02b9-b7a2-4780-b954-3ebaa11e0afb</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select '<root>Hello world!</root>' my_xml
union select '<root>Goodbye!</root>'</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="my_xml">
<rd:TypeName>System.String</rd:TypeName>
<DataField>my_xml</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<CodeModules>
<CodeModule>System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</CodeModule>
</CodeModules>
<Code>public shared Function ParseXml(xml as string) as string
Dim doc as System.Xml.XmlDocument = new System.Xml.XmlDocument()
doc.LoadXml(xml)
ParseXml = doc.SelectSingleNode("root").InnerText
end function</Code>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
No comments:
Post a Comment