Read XML data from SQL 2005 table

[nilaangel78]

Hi All,

I have SQL 2005 table with columns TypeId(int) & Description(ntext datatype).
The Description column has XML file content as value in the table.
Now i want to read the data into a dataset in VB.Net. How to do it in VB.Net. I am using Winforms and VB.Net in VS 2005.
Below i have given the xml file content.

 <?xml version="1.0" encoding="Windows-1252" ?> 
- <SummaryPageComponent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
- <categories>
- <anyType xsi:type="SummaryPageComponentCategory">
  <CategoryName>HotfixesCategory</CategoryName> 
  <CategoryType>HotfixesCategory</CategoryType> 
  <ResourceIdColumnOrdinal>2</ResourceIdColumnOrdinal> 
- <CategoryTable>
- <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="categoryTable" msdata:UseCurrentLocale="true">
- <xs:complexType>
- <xs:choice minOccurs="0" maxOccurs="unbounded">
- <xs:element name="categoryTable">
- <xs:complexType>
- <xs:sequence>
  <xs:element name="HF_x0020_Name" type="xs:string" /> 
  <xs:element name="Installation_x0020_Date" type="xs:dateTime" minOccurs="0" /> 
  <xs:element name="iResourceId" type="xs:int" /> 
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:choice>
  </xs:complexType>
- <xs:unique name="Constraint1" msdata:PrimaryKey="true">
  <xs:selector xpath=".//categoryTable" /> 
  <xs:field xpath="iResourceId" /> 
  <xs:field xpath="HF_x0020_Name" /> 
  </xs:unique>
  </xs:element>
  </xs:schema>
- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
- <DocumentElement>
- <categoryTable diffgr:id="categoryTable1" msdata:rowOrder="0">
  <HF_x0020_Name>Interactions Center - Update 100145</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
- <categoryTable diffgr:id="categoryTable2" msdata:rowOrder="1">
  <HF_x0020_Name>Interactions Center (UN100001)</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
- <categoryTable diffgr:id="categoryTable3" msdata:rowOrder="2">
  <HF_x0020_Name>Interactions Center (UN100101)</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
- <categoryTable diffgr:id="categoryTable4" msdata:rowOrder="3">
  <HF_x0020_Name>Interactions Center (UN100106) - UserAdmin persistency</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
- <categoryTable diffgr:id="categoryTable5" msdata:rowOrder="4">
  <HF_x0020_Name>Interactions Center (UN100107)</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
- <categoryTable diffgr:id="categoryTable6" msdata:rowOrder="5">
  <HF_x0020_Name>Interactions Center (UN100146)</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
- <categoryTable diffgr:id="categoryTable7" msdata:rowOrder="6">
  <HF_x0020_Name>Interactions Center (UN100167) - Monitoring SessionController in IPC</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
- <categoryTable diffgr:id="categoryTable8" msdata:rowOrder="7">
  <HF_x0020_Name>Interactions Center (UN100223) - syncronization issues</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
- <categoryTable diffgr:id="categoryTable9" msdata:rowOrder="8">
  <HF_x0020_Name>Interactions Center (UN100227) - Updates installation issue</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
- <categoryTable diffgr:id="categoryTable10" msdata:rowOrder="9">
  <HF_x0020_Name>Interactions Center (UN100235) - Incorrect stop time</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
- <categoryTable diffgr:id="categoryTable11" msdata:rowOrder="10">
  <HF_x0020_Name>Interactions Center UN100140 - Dynamic Mappings By Switch</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
- <categoryTable diffgr:id="categoryTable12" msdata:rowOrder="11">
  <HF_x0020_Name>Interactions Center Update 1</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
- <categoryTable diffgr:id="categoryTable13" msdata:rowOrder="12">
  <HF_x0020_Name>Interactions Center Update 2 - batch insertions into database</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
- <categoryTable diffgr:id="categoryTable14" msdata:rowOrder="13">
  <HF_x0020_Name>NICE Perform® Release 3.1 - Change check for audio technique</HF_x0020_Name> 
  <Installation_x0020_Date>2009-03-17T00:00:00+05:30</Installation_x0020_Date> 
  <iResourceId>32</iResourceId> 
  </categoryTable>
  </DocumentElement>
  </diffgr:diffgram>
  </CategoryTable>
- <Headers>
  <string>Name</string> 
  <string>Installation Date</string> 
  <string>ResourceId</string> 
  </Headers>
  <DisplayHeaders>true</DisplayHeaders> 
  </anyType>
  </categories>
  <ResourceType>23</ResourceType> 
- <ResourceIds>
  <int>32</int> 
  </ResourceIds>
- <HeaderData>
- <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="vwSummaryPageSystemResource" msdata:UseCurrentLocale="true">
- <xs:complexType>
- <xs:choice minOccurs="0" maxOccurs="unbounded">
- <xs:element name="vwSummaryPageSystemResource">
- <xs:complexType>
- <xs:sequence>
  <xs:element name="iResourceId" type="xs:int" /> 
  <xs:element name="iSiteId" type="xs:int" /> 
  <xs:element name="iResourceType" type="xs:int" minOccurs="0" /> 
- <xs:element name="vcResourceName" minOccurs="0">
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="256" /> 
  </xs:restriction>
  </xs:simpleType>
  </xs:element>
- <xs:element name="nvcHostName" minOccurs="0">
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="256" /> 
  </xs:restriction>
  </xs:simpleType>
  </xs:element>
- <xs:element name="nvcVersion" minOccurs="0">
- <xs:simpleType>
- <xs:restriction base="xs:string">
  <xs:maxLength value="100" /> 
  </xs:restriction>
  </xs:simpleType>
  </xs:element>
  <xs:element name="bIsGeneralInfo" type="xs:boolean" minOccurs="0" /> 
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:choice>
  </xs:complexType>
  </xs:element>
  </xs:schema>
- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
- <DocumentElement>
- <vwSummaryPageSystemResource diffgr:id="vwSummaryPageSystemResource1" msdata:rowOrder="0" diffgr:hasChanges="modified">
  <iResourceId>32</iResourceId> 
  <iSiteId>1</iSiteId> 
  <iResourceType>23</iResourceType> 
  <vcResourceName>NICECLS</vcResourceName> 
  <nvcHostName>192.168.91.36</nvcHostName> 
  <nvcVersion>9.15.614 (Update Pack 6)</nvcVersion> 
  <bIsGeneralInfo>false</bIsGeneralInfo> 
  </vwSummaryPageSystemResource>
  </DocumentElement>
- <diffgr:before>
- <vwSummaryPageSystemResource diffgr:id="vwSummaryPageSystemResource1" msdata:rowOrder="0">
  <iResourceId>32</iResourceId> 
  <iSiteId>1</iSiteId> 
  <iResourceType>23</iResourceType> 
  <vcResourceName>NICECLS</vcResourceName> 
  <nvcHostName>192.168.91.36</nvcHostName> 
  <bIsGeneralInfo>false</bIsGeneralInfo> 
  </vwSummaryPageSystemResource>
  </diffgr:before>
  </diffgr:diffgram>
  </HeaderData>
  </SummaryPageComponent>

Please help me.

Thanks

Nila

[iam_clint]

Dim ds as new dataset
Dim xmlstr as string
xmlstr = fromrecordset
ds.ReadXml(xmlstr)
 

This is how you make a dataset read xml.. I assume this is what you were wanting. To put a sql table into a dataset is pretty easy if thats what you were wanting

Dim ds as new dataset
Dim dbadap as sqldataadapter
dbadap = New SqlDataAdapter("Select * from countries order by country_name", DBConn)
dbadap.fill(ds, "countries")
 

[nilaangel78]

Hi Clint,

The content in the table is in XML format. Now i want to read this XML content and show it in the datagridview. But as the XML content has the schema i am not able read it. Can you help me.

Thanks
Nila

[iam_clint]

I answered how to read the xml content from the database put it in a dataset.. from there you can bind it to a grid view. I am unsure about schema's as i've never really worked with them or really have an understanding of what a schema does. So beyond this point I can't be too much of a help unless someone comes in here and explains the difference between xml and xml with a schema ;( sorry.