none
Error with reading XML file from Sharepoint RRS feed

  • Question

  • Hi,

    We are trying to read XML file from a sharepoint site. and Code dosent seems to be reading the file at all. It just moves to End Try on this command While xmlfile.Read(). It display the number of nodes in xml but failes to read the contents of XML.

    Below is my code:

     Private Sub Load3ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles Load3ToolStripMenuItem.Click
            Dim sccm_srv_link As String
            Dim n = 0
            Dim Row1 As DataRow
            Try
                sccm_srv_link = "https://mysite.com/sites/XXX/_vti_bin/owssvr.dll?Cmd=Display&List={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxx}&XMLDATA=TRUE&RowLimit=0"
                Dim xmlfile As XmlReader
                Dim resolver As New XmlUrlResolver
                resolver.Credentials = CredentialCache.DefaultCredentials
                Dim xmsettings As New XmlReaderSettings
                xmsettings.XmlResolver = resolver
                xmlfile = XmlReader.Create(sccm_srv_link, xmsettings)
                Dim doc As XmlDocument = New XmlDocument()
                doc.Load(xmlfile)
                Dim root As XmlElement = doc.DocumentElement
                Dim elemList As XmlNodeList = root.GetElementsByTagName("z:row")
                Dim max As Integer = elemList.Count - 1
                While xmlfile.Read()
                    Select Case xmlfile.NodeType
                        Case XmlNodeType.Element 'Display beginning of element.
                            If xmlfile.Name = "z:row" Then
                                If xmlfile.HasAttributes Then 'If attributes exist
                                    Row1 = table1.NewRow()
                                    While xmlfile.MoveToNextAttribute()
    
                                        If xmlfile.Name = "ows_LinkTitle" Then
                                            Row1.Item("Host_Name") = xmlfile.Value
                                        End If
                                        If xmlfile.Name = "ows_Service_x0020_TAG" Then
                                            Row1.Item("Service_Tag") = xmlfile.Value
                                        End If
                                        If xmlfile.Name = "ows_Model" Then
                                            Row1.Item("Model") = xmlfile.Value
                                        End If
                                        If xmlfile.Name = "ows_Full_x0020_Name" Then
                                            Row1.Item("Full_Name") = xmlfile.Value
                                        End If
                                        If xmlfile.Name = "ows_LAN_x0020_ID" Then
                                            Row1.Item("User_Name") = xmlfile.Value
                                        End If
                                        If xmlfile.Name = "ows_Location" Then
                                            Row1.Item("Location") = xmlfile.Value
                                        End If
                                    End While
                                    n = n + 1
                                    table1.Rows.Add(Row1)
                                End If
                            End If
                    End Select
                    DataGridView1.DataSource = table1
                    DataGridView1.Refresh()
                End While
            Catch ex As Exception
                MsgBox(ex.Message)
                Exit Sub
            End Try
        End Sub

    And here is the XML output from the site:

    <xml xmlns:s='uuid:XXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
         xmlns:dt='uuid:XXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
         xmlns:rs='urn:schemas-microsoft-com:rowset'
         xmlns:z='#RowsetSchema'>
    <s:Schema id='RowsetSchema'>
       <s:ElementType name='row' content='eltOnly' rs:CommandTimeout='30'>
          <s:AttributeType name='ows_LinkTitle' rs:name='Computer Name' rs:number='1'>
             <s:datatype dt:type='string' dt:maxLength='512' />
          </s:AttributeType>
          <s:AttributeType name='ows_LAN_x0020_ID' rs:name='LAN ID' rs:number='2'>
             <s:datatype dt:type='string' dt:maxLength='512' />
          </s:AttributeType>
          <s:AttributeType name='ows_Full_x0020_Name' rs:name='Full Name' rs:number='3'>
             <s:datatype dt:type='string' dt:maxLength='1073741823' />
          </s:AttributeType>
          <s:AttributeType name='ows_Service_x0020_TAG' rs:name='Service TAG' rs:number='4'>
             <s:datatype dt:type='string' dt:maxLength='512' />
          </s:AttributeType>
          <s:AttributeType name='ows_Model' rs:name='Model' rs:number='5'>
             <s:datatype dt:type='string' dt:maxLength='512' />
          </s:AttributeType>
          <s:AttributeType name='ows_Location' rs:name='Location' rs:number='6'>
             <s:datatype dt:type='string' dt:maxLength='512' />
          </s:AttributeType>
          <s:AttributeType name='ows_Warranty_x0020_Start_x0020_Date' rs:name='Warranty Start Date' rs:number='7'>
             <s:datatype dt:type='datetime' dt:maxLength='8' />
          </s:AttributeType>
          <s:AttributeType name='ows_Warranty_x0020_End_x0020_Date' rs:name='Warranty End Date' rs:number='8'>
             <s:datatype dt:type='datetime' dt:maxLength='8' />
          </s:AttributeType>
          <s:AttributeType name='ows_Status' rs:name='Status' rs:number='9'>
             <s:datatype dt:type='string' dt:maxLength='512' />
          </s:AttributeType>
          <s:AttributeType name='ows_Comments' rs:name='Comments' rs:number='10'>
             <s:datatype dt:type='string' dt:maxLength='512' />
          </s:AttributeType>
          <s:AttributeType name='ows_Editor' rs:name='Modified By' rs:number='11'>
             <s:datatype dt:type='string' dt:lookup='true' dt:maxLength='512' />
          </s:AttributeType>
          <s:AttributeType name='ows_Modified' rs:name='Modified' rs:number='12'>
             <s:datatype dt:type='datetime' dt:maxLength='8' />
          </s:AttributeType>
       </s:ElementType>
    </s:Schema>
    <rs:data>
       <z:row ows_LinkTitle='COMPUTER_NAME' ows_LAN_x0020_ID='User_ID' ows_Full_x0020_Name='User_Name' ows_Service_x0020_TAG='SERVICE_TAG' ows_Model='COMPUTE_MODEL' ows_Location='LOCATION' ows_Warranty_x0020_Start_x0020_Date='2019-12-30 00:00:00' ows_Warranty_x0020_End_x0020_Date='2019-12-30 00:00:00' ows_Status='Status 01' ows_Comments='NO_COMMENTS' ows_Editor='66;#USER_NAME' ows_Modified='2019-12-30 00:00:00' />
       <z:row ows_LinkTitle='COMPUTER_NAME' ows_LAN_x0020_ID='User_ID' ows_Full_x0020_Name='User_Name' ows_Service_x0020_TAG='SERVICE_TAG' ows_Model='COMPUTE_MODEL' ows_Location='LOCATION' ows_Warranty_x0020_Start_x0020_Date='2019-12-30 00:00:00' ows_Warranty_x0020_End_x0020_Date='2019-12-30 00:00:00' ows_Status='Status 02' ows_Comments='NO_COMMENTS' ows_Editor='66;#USER_NAME' ows_Modified='2019-12-30 00:00:00' />
       <z:row ows_LinkTitle='COMPUTER_NAME' ows_LAN_x0020_ID='User_ID' ows_Full_x0020_Name='User_Name' ows_Service_x0020_TAG='SERVICE_TAG' ows_Model='COMPUTE_MODEL' ows_Location='LOCATION' ows_Warranty_x0020_Start_x0020_Date='2019-12-30 00:00:00' ows_Warranty_x0020_End_x0020_Date='2019-12-30 00:00:00' ows_Status='Status 01' ows_Comments='NO_COMMENTS' ows_Editor='66;#USER_NAME' ows_Modified='2019-12-30 00:00:00' />
       <z:row ows_LinkTitle='COMPUTER_NAME' ows_Full_x0020_Name='&lt;div&gt;In Stock&lt;br&gt;&lt;/div&gt;' ows_Service_x0020_TAG='SERVICE_TAG' ows_Model='COMPUTE_MODEL' ows_Location='LOCATION' ows_Warranty_x0020_Start_x0020_Date='2019-12-30 00:00:00' ows_Warranty_x0020_End_x0020_Date='2019-12-30 00:00:00' ows_Status='Status 04' ows_Editor='48;#USER_NAME' ows_Modified='2019-12-30 00:00:00' />
       <z:row ows_LinkTitle='COMPUTER_NAME ' ows_LAN_x0020_ID='User_ID' ows_Full_x0020_Name='&lt;div&gt;User_Name&lt;/div&gt;' ows_Service_x0020_TAG='SERVICE_TAG ' ows_Model='COMPUTE_MODEL' ows_Location='LOCATION' ows_Warranty_x0020_Start_x0020_Date='2019-12-30 00:00:00' ows_Warranty_x0020_End_x0020_Date='2019-12-30 00:00:00' ows_Status='Status 03' ows_Editor='67;#USER_NAME' ows_Modified='2019-12-30 00:00:00' />
    </rs:data>
    </xml>
    

    How ever the above code was able to display output for the below xml file.

    <?xml version="1.0" encoding="utf-8"?>
    <Report xsi:schemaLocation="Hardware_x0020_Inventory http://mysite.com/Reports?%2FReports%2FCustom%20Reports%2FInventory&amp;rs%3ACommand=Render&amp;rs%3AFormat=XML&amp;rc%3ASchema=True" Name="Inventory"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xmlns="Hardware_x0020_Inventory">
    	<Tablix1>
    		<Details_Collection>
    			<Details Device_Name="Device_name" Model="Device_Model" Serial_Number="Device_Serial" User_Name="User_Name" Full_Name="User_FullName" Location="Location" Last_Used="2019-02-20T00:00:00" />
    			<Details Device_Name="Device_name" Model="Device_Model" Serial_Number="Device_Serial" User_Name="User_Name" Full_Name="User_FullName" Location="Location" Last_Used="2019-02-20T00:00:00" />
    			<Details Device_Name="Device_name" Model="Device_Model" Serial_Number="Device_Serial" User_Name="User_Name" Full_Name="User_FullName" Location="Location" Last_Used="2019-02-20T00:00:00" />
    			<Details Device_Name="Device_name" Model="Device_Model" Serial_Number="Device_Serial" User_Name="User_Name" Full_Name="User_FullName" Location="Location" Last_Used="2019-02-20T00:00:00" />
    			<Details Device_Name="Device_name" Model="Device_Model" Serial_Number="Device_Serial" User_Name="User_Name" Full_Name="User_FullName" Location="Location" Last_Used="2019-02-20T00:00:00" />
    			<Details Device_Name="Device_name" Model="Device_Model" Serial_Number="Device_Serial" User_Name="User_Name" Full_Name="User_FullName" Location="Location" Last_Used="2019-02-20T00:00:00" />
    		</Details_Collection>
    	</Tablix1>
    </Report>

    Coderv9

    Monday, April 1, 2019 2:03 PM

All replies

  • Firstly, I would like to offer my most sincere apologies that you must deal with SharePoint. 

    Have you considered using the Sharepoint.Client namepsace?


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Monday, April 1, 2019 2:46 PM
  • Firstly, I would like to offer my most sincere apologies that you must deal with SharePoint. 

    Have you considered using the Sharepoint.Client namepsace?


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    :) Yeahh Sharepoint is really painfull especially when dealing with lists. As we don't  want to have a dependency for Sharepoint so we pull the details as XML and then plot it in DataGrid. We were also able to query the Sharepoint portal but the details seems to have messed up when the Field contains HTML values. 

    Below piece of code uses ADOB driver which is anoter dependancy coz we are using mysql for all other DB operation.

       Public Sub retrieveData()
            Dim cnt As New ADODB.Connection
            Dim rs As ADODB.Recordset
            Dim sqlQuery As String
            cnt = New ADODB.Connection
            rs = New ADODB.Recordset
            sqlQuery = "Select [ID],[Computer Name],[LAN ID],[User Name],[Full Name],[Service TAG],[Model],
                                [Location],[Warranty Start Date],[Warranty End Date],[Status],[Comments] from [Asset Management];"
            With cnt
                .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=https://mysite.com/sites/XXX/_vti_bin/owssvr.dll?Cmd=Display&List={xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxx};"
                .Open()
            End With
            rs.Open(sqlQuery, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly)
            Dim myDa As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
            Dim myDs As DataTable = New DataTable
            myDa.Fill(myDs, rs)
            DataGridView1.DataSource = myDs
            If CBool(rs.State And ADODB.ObjectStateEnum.adStateOpen) = True Then rs.Close()
            rs = Nothing
            If CBool(cnt.State And ADODB.ObjectStateEnum.adStateOpen) = True Then cnt.Close()
            cnt = Nothing
        End Sub

    Eg: Below row contains html field in 

    ows_Full_x0020_Name

     <z:row ows_LinkTitle='COMPUTER_NAME' ows_Full_x0020_Name='&lt;div&gt;In Stock&lt;br&gt;&lt;/div&gt;' ows_Service_x0020_TAG='SERVICE_TAG' ows_Model='COMPUTE_MODEL' ows_Location='LOCATION' ows_Warranty_x0020_Start_x0020_Date='2019-12-30 00:00:00' ows_Warranty_x0020_End_x0020_Date='2019-12-30 00:00:00' ows_Status='Status 04' ows_Editor='48;#USER_NAME' ows_Modified='2019-12-30 00:00:00' />

    Which causes query to print the html value. So we though of pulling it as xml so the text can be formated as we need which would be the next question ;-)


    Coderv9

    Monday, April 1, 2019 3:30 PM
  • In my opinion Sharepoint is an absolute disaster. I could go on for hours how its a terrible "solution" ranging from UI nightmares, necessity for WorkFlows(I just puked a little), random limitations with passing data to/from Infopath(that's been discontinued) to browser requirements, down to the mountain of dependencies required to run it. 

    You may really want to look into CSOM 

    here is an example in C# 

    If you wanted to give it a go, you can install SharePoint.Client and SharePoint.Client.RunTime from Nuget and I could help if you have any problems with converting to VB


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Monday, April 1, 2019 3:57 PM
  • In my opinion Sharepoint is an absolute disaster. I could go on for hours how its a terrible "solution" ranging from UI nightmares, necessity for WorkFlows(I just puked a little), random limitations with passing data to/from Infopath(that's been discontinued) to browser requirements, down to the mountain of dependencies required to run it. 

    You may really want to look into CSOM 

    here is an example in C# 

    If you wanted to give it a go, you can install SharePoint.Client and SharePoint.Client.RunTime from Nuget and I could help if you have any problems with converting to VB


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Hi Thank you for the input. We are trying to avoid having a dependency for sharepoint :( But for SharePoint.Client.RunTime we need to have the client installed.

    Coderv9

    Tuesday, April 2, 2019 7:50 AM
  • Hi Thank you for the input. We are trying to avoid having a dependency for sharepoint :( But for SharePoint.Client.RunTime we need to have the client installed.


    Coderv9

    Im not sure I follow, there is no client. It's just a namespace. Once added as a reference the libraries will be deployed with the application. There is nothing you have to install clientside

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Tuesday, April 2, 2019 11:59 AM