Shaun_SPS

I am creating an infopath form that uses a web service to grab data from a SharePoint list. I am able to get back the correct data but I am can't figure out how to iterate through the data returned to insert it into a repeating row. Any help or a point in the right direction would be greatly appreciated.

Here is my code (site omitted to protect the innocent)

Code Snippet

Dim listService As New GetList.Lists

Dim cr As New System.Net.NetworkCredential("", "", "")

listService.Credentials = cr

listService.Url = "siteURL/_vti_bin/lists.asmx"

Dim xmlDoc As New System.Xml.XmlDocument

Dim listName As String = "{9D39C54C-ABBB-4E79-A3DA-A781ADB09DF3}"

Dim viewName As String = ""

Dim rowLimit As String = ""

Dim Unit As String = thisXDocument.DOM.selectSingleNode("/my:myFields/my:Unit").text

Dim i As Integer

'Use the CreateElement method of the document object to create 'elements for the parameters that use XML.

Dim query As System.Xml.XmlElement = xmlDoc.CreateElement("Query")

Dim viewFields As System.Xml.XmlElement = xmlDoc.CreateElement("ViewFields")

Dim queryOptions As System.Xml.XmlElement = xmlDoc.CreateElement("QueryOptions")

'To specify values for the parameter elements (optional), assign CAML fragments to the InnerXml property of each element.

query.InnerXml = "" + "CM" + _

"" + Unit + ""

'query.InnerXml = ""

viewFields.InnerXml = ""

queryOptions.InnerXml = ""

'Declare an XmlNode object and initialize it with the XML response 'from the GetListItems method. The last parameter specifies

'the GUID of the Web site containing the list. Setting it to

'Nothing causes the Web site specified by the Url property

'to be used.

Dim nodeListItems As System.Xml.XmlNode = listService.GetListItems(listName, viewName, query, viewFields, rowLimit, queryOptions)

MsgBox(nodeListItems.OuterXml, MsgBoxStyle.Critical)

thisXDocument.DOM.selectSingleNode("//my:myFields/my:field1").text = nodeListItems.OuterXml

Here is what gets returned.

Code Snippet

<listitems xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns="http://schemas.microsoft.com/sharepoint/soap/">; <rs:data ItemCount="1"> <z:row ows_TrackingNumber="ALLCHD_6_1_2007-04-07" ows_Unit="Unit 1" ows_ContainerType="CM" ows_Active="-1" ows_ID="825" ows_owshiddenversion="1" /> </rs:data> </listitems>

Thanks to anyone that can help out!



Re: XML and the .NET Framework SharePoint getListItems web service help

Derek Smyth

Alright mate,

Not 100% sure with what you mean a repeating row but you can iterate over nodes like this, the example will assume you get more than one row element just to give you a better example.

I'll use this XML as an example:

Code Snippet

< xml version="1.0" encoding="utf-8" >

<listitems xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns="http://schemas.microsoft.com/sharepoint/soap/">

<rs:data ItemCount="1">

<z:row ows_TrackingNumber="ALLCHD_6_1_2007-04-07" ows_Unit="Unit 1" ows_ContainerType="CM" ows_Active="-1" ows_ID="825" ows_owshiddenversion="1" />

<z:row ows_TrackingNumber="ALLCHD_6_2_2007-04-07" ows_Unit="Unit 1" ows_ContainerType="CM" ows_Active="-1" ows_ID="825" ows_owshiddenversion="1" />

<z:row ows_TrackingNumber="ALLCHD_6_3_2007-04-07" ows_Unit="Unit 1" ows_ContainerType="CM" ows_Active="-1" ows_ID="825" ows_owshiddenversion="1" />

<z:row ows_TrackingNumber="ALLCHD_6_4_2007-04-07" ows_Unit="Unit 1" ows_ContainerType="CM" ows_Active="-1" ows_ID="825" ows_owshiddenversion="1" />

<z:row ows_TrackingNumber="ALLCHD_6_5_2007-04-07" ows_Unit="Unit 1" ows_ContainerType="CM" ows_Active="-1" ows_ID="825" ows_owshiddenversion="1" />

</rs:data>

<rs:data ItemCount="2">

<z:row ows_TrackingNumber="ALLCHD_6_6_2007-04-07" ows_Unit="Unit 1" ows_ContainerType="CM" ows_Active="-1" ows_ID="825" ows_owshiddenversion="1" />

<z:row ows_TrackingNumber="ALLCHD_6_7_2007-04-07" ows_Unit="Unit 1" ows_ContainerType="CM" ows_Active="-1" ows_ID="825" ows_owshiddenversion="1" />

<z:row ows_TrackingNumber="ALLCHD_6_8_2007-04-07" ows_Unit="Unit 1" ows_ContainerType="CM" ows_Active="-1" ows_ID="825" ows_owshiddenversion="1" />

<z:row ows_TrackingNumber="ALLCHD_6_9_2007-04-07" ows_Unit="Unit 1" ows_ContainerType="CM" ows_Active="-1" ows_ID="825" ows_owshiddenversion="1" />

</rs:data>

</listitems>

Here is the code that loops over each data element and the each child row element under the data element

Code Snippet

Sub Main()

Dim xmlDoc As New XmlDocument

xmlDoc.Load("SampleData.xml")

Dim ns As New XmlNamespaceManager(xmlDoc.NameTable)

ns.AddNamespace("sp", "http://schemas.microsoft.com/sharepoint/soap/")

ns.AddNamespace("rs", "urn:schemas-microsoft-com:rowset")

ns.AddNamespace("z", "#RowsetSchema")

Dim dataNodes As XmlNodeList = xmlDoc.SelectNodes("//sp:listitems/rs:data", ns) 'selects all data elements in the document

'for each data node

For Each dataNode As XmlNode In dataNodes

Console.WriteLine(dataNode.Attributes("ItemCount").Value)

'for each child row node of the data node

For Each rowNode As XmlNode In dataNode.ChildNodes

Console.WriteLine(rowNode.Attributes("ows_TrackingNumber").Value)

Console.WriteLine(rowNode.Attributes("ows_Unit").Value)

Next

Next

Console.ReadLine()

End Sub






Re: XML and the .NET Framework SharePoint getListItems web service help

Shaun_SPS

Thanks for the help! I have one question and I apologize for being a twit but in your example you are loading "SampleData.xml" My data is being returned by a web service. Do I load the data into the SampleData.xml file or can I load what is returned by the getlistitems web service directly. Thanks again!



Re: XML and the .NET Framework SharePoint getListItems web service help

Derek Smyth

No worries about the help, the SampleData.xml was just a temporary file I needed to use to get the xml into the document, if your getting your xml from a web service then you don't need to save it to file and then re-read it in, you should load it directly.




Re: XML and the .NET Framework SharePoint getListItems web service help

Shaun_SPS

Thanks. I've done that and it looks like everything is working however I now have another problem. Like I mentioned before, I am interating through the data returned and entering it into a repeating infopath table. The problem I have now is if the list field is blank, the field is not included in the row. For example, I have a field called ProfileNumber. If there is a value present, it gets inserted into the repeating table, if there is no value for this field, I get the following error, "Object reference not set to an instance of an object." Do you know if it's possible to persist the column even if there is no value present Please let me know. Thanks again for all your help!



Re: XML and the .NET Framework SharePoint getListItems web service help

Derek Smyth

hey man, yeah you need to do a check to see if the attribute is there before you access it.

change....

Code Snippet

'for each child row node of the data node

For Each rowNode As XmlNode In dataNode.ChildNodes

Console.WriteLine(rowNode.Attributes("ows_TrackingNumber").Value)

Console.WriteLine(rowNode.Attributes("ows_Unit").Value)

Next

to....

Code Snippet

'for each child row node of the data node

For Each rowNode As XmlElement In dataNode.ChildNodes

if rowNode.HasAttribute("ows_TrackingNumber") = true)

Console.WriteLine(rowNode.Attributes("ows_TrackingNumber").Value)

end if

if rowNode.HasAttribute("ows_Unit") = true)

Console.WriteLine(rowNode.Attributes("ows_Unit").Value)

End if

Next

Iterate over the ChildNodes using XmlElement types and use the HasAttribute() method to check, should have included that in my original code snippet.




Re: XML and the .NET Framework SharePoint getListItems web service help

bart

Hi Derek,

I found answer to be very helpful in manipulating the XML list.

But I couldn't successfully retrieve the data from the list.

I am getting SOAP exception thrown as " fieldname not found."...

I don't get the point...Loadxml() coz,,while using sharepoint, it will dynamically create list .

And on selection of a particular list i would like to retrieve the column name and data underthat...

But no luck so far...

Any help will be appreciated million times...

the following is my Code....

Thanks heaps in advance...

Dim myCred As New System.Net.NetworkCredential("", "", "")

Dim myCache1 As New System.Net.CredentialCache

Dim listService1 As lists.Lists = New lists.Lists()

myCache1.Add(New Uri("http://XXXXXXX.com.au/xxxxxxx/_vti_bin/Lists.asmx"), "NTLM", myCred)

listService1.Credentials = myCache1

listService1.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim listItem1 As Xml.XmlNode = listService1.GetListCollection

For Each node As XmlNode In listItem1

Dim xmldoc As New XmlDocument

'XmlNode(query = xmldoc.CreateNode)

Dim listName1 As String = TableList.SelectedValue

'"{0DE94110-149A-4E51-B896-274AD75501A8}"

' Dim viewName1 As String = ""

' Dim rowLimit1 As String = "10"

Dim query As System.Xml.XmlElement = xmldoc.CreateNode(XmlNodeType.Element, "Query", "")

Dim viewFields1 As System.Xml.XmlElement = xmldoc.CreateNode(XmlNodeType.Element, "ViewFields", "")

' Dim viewFields1 As System.Xml.XmlElement = xmldoc.CreateElement("ViewFields")

Dim queryOptions As System.Xml.XmlElement = xmldoc.CreateNode(XmlNodeType.Element, "QueryOptions", "")

query.InnerXml = "<Where><And><FieldRef Name=""ows_test""/>" + _

"<Value Type=""text""></Value></And></Where>"

viewFields1.InnerXml = "<FieldRef Name=""ows_test"" />"

queryOptions.InnerXml = "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>" '

'+DateInUtc>TRUE</DateInUtc>"

Try

Dim nodeListItems1 As System.Xml.XmlNode = listService1.GetListItems(listName1, "", query, viewFields1, "", queryOptions)

Dim doc As XmlDocument = New XmlDocument

' doc.LoadXml(Replace(nodeListItems1.InnerXml, ControlChars.Lf & ControlChars.Lf, ""))

doc.LoadXml(nodeListItems1.InnerXml)

Dim nsmgr As XmlNamespaceManager = New XmlNamespaceManager(doc.NameTable)

nsmgr.AddNamespace("z", "#RowsetSchema")

nsmgr.AddNamespace("rs", "urnTongue Tiedchemas-microsoft-com:rowset")

Dim node1 As XmlNode

Dim xl As XmlNodeList = doc.SelectNodes("/rsBig Smileata/z:row", nsmgr)

For Each node1 In xl

'Response.Write(node1.InnerXml)

Response.Write(node1.Attributes("ID").Value)

For Each rownode As XmlNode In node1.ChildNodes

Response.Write(rownode.Attributes("ows_test").Value)

Next

Next

'Console.WriteLine(nodeListItems1.InnerXml)

Catch ex As System.Web.Services.Protocols.SoapException

Response.Write("Message:" & vbCrLf & ex.Message + vbCrLf + "Detail:" + vbCrLf + ex.Detail.InnerText + ControlChars.Lf + "StackTrace:" + ControlChars.Lf + ex.StackTrace)

End Try

Next

Return Nothing





Re: XML and the .NET Framework SharePoint getListItems web service help

Shaun_SPS

Here is my working code based on the help Derek provided above. Hope it helps

Code Snippet

Sub GrabUnitInventory()

thisXDocument.View.DisableAutoUpdate()

Dim listService As New GETITEMS.Lists

Dim cr As New System.Net.NetworkCredential("username", "password", "domain")

listService.Credentials = cr

Dim xmlDoc As New System.Xml.XmlDocument

Dim listName As String = "{list id}"

Dim viewName As String = "{view id}"

Dim rowLimit As String = "10000"

Dim Unit As String = thisXDocument.DOM.selectSingleNode("//my:myFields/my:ProcessInfo/my:LocationUnit").text

Dim i As Integer = 1

'Use the CreateElement method of the document object to create 'elements for the parameters that use XML.

Dim query As System.Xml.XmlElement = xmlDoc.CreateElement("Query")

Dim viewFields As System.Xml.XmlElement = xmlDoc.CreateElement("ViewFields")

Dim queryOptions As System.Xml.XmlElement = xmlDoc.CreateElement("QueryOptions")

'To specify values for the parameter elements (optional), assign CAML fragments to the InnerXml property of each element.

query.InnerXml = "<OrderBy><FieldRef Name=""ActualMaterial"" Ascending=""TRUE""></FieldRef></OrderBy><Where><And>" + _

"<Neq><FieldRef Name=""Active"" />" + "<Value Type=""Text"">" + "0" + "</Value></Neq>" + _

"<Eq><FieldRef Name=""Unit"" /><Value Type=""Text"">" + Unit + "</Value></Eq>" + _

"</And></Where>"

viewFields.InnerXml = "<FieldRef Name=""TrackingNumber"" /><FieldRef Name=""ActualMaterial"" /><FieldRef Name=""Unit"" /><FieldRef Name=""ContainerType"" /><FieldRef Name=""Size"" /><FieldRef Name=""Weight"" />" + _

"<FieldRef Name=""ProfileNumber"" /><FieldRef Name=""InboundNumber"" /><FieldRef Name=""ProcessNumber"" /><FieldRef Name=""Material"" /><FieldRef Name=""ID"" /><FieldRef Name=""WeightTicketNumber"" />"

queryOptions.InnerXml = ""

'Declare an XmlNode object and initialize it with the XML response 'from the GetListItems method. The last parameter specifies

'the GUID of the Web site containing the list. Setting it to

'Nothing causes the Web site specified by the Url property

'to be used.

Dim nodeListItems As System.Xml.XmlNode = listService.GetListItems(listName, viewName, query, viewFields, rowLimit, queryOptions)

'MsgBox(nodeListItems.OuterXml, MsgBoxStyle.Critical)

Dim xd As XmlDocument = New XmlDocument

xd.LoadXml("< xml version='1.0' >" & nodeListItems.OuterXml)

Dim ns As New XmlNamespaceManager(xd.NameTable)

ns.AddNamespace("sp", "http://schemas.microsoft.com/sharepoint/soap/")

ns.AddNamespace("rs", "urn:schemas-microsoft-com:rowset")

ns.AddNamespace("z", "#RowsetSchema")

Dim oParentNode As IXMLDOMNode

oParentNode = thisXDocument.DOM.selectSingleNode("/my:myFields")

Dim strFields As String

strFields = "<my:MovementDetail xmlns:my=""http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T20-30-41""><my:TrackingNumber/><my:ActualMaterial/><my:Source/><my:ContainerType/><my:Size/><my:MovementWeight/><my:MovementType/><my:MovementTrackingNumber/><my:WasteTrackingID/><my:MovementID/><my:ProfileNumber/><my:InboundNumber/><my:ProcessNumber/><my:Material/><my:MovementLineNumber/><my:WasteTrackingOldID/><my:ShortNumber/><my:WeighTicketNumber/></my:MovementDetail>"

Dim domXMLNode As IXMLDOMDocument

domXMLNode = thisXDocument.CreateDOM()

domXMLNode.async = False

domXMLNode.loadXML(strFields)

Dim dataNodes As XmlNodeList = xd.SelectNodes("//sp:listitems/rs:data", ns)

For Each dataNode As XmlNode In dataNodes

For Each rowNode As XmlElement In dataNode.ChildNodes

thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & i & "]/my:TrackingNumber").text = rowNode.Attributes("ows_TrackingNumber").Value

If rowNode.HasAttribute("ows_ActualMaterial") = True Then

thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & i & "]/my:ActualMaterial").text = rowNode.Attributes("ows_ActualMaterial").Value

End If

If rowNode.HasAttribute("ows_Unit") = True Then

thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & i & "]/my:Source").text = rowNode.Attributes("ows_Unit").Value

End If

If rowNode.HasAttribute("ows_ContainerType") = True Then

thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & i & "]/my:ContainerType").text = rowNode.Attributes("ows_ContainerType").Value

End If

If rowNode.HasAttribute("ows_Size") = True Then

thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & i & "]/my:Size").text = rowNode.Attributes("ows_Size").Value

End If

If rowNode.HasAttribute("ows_Weight") = True Then

thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & i & "]/my:MovementWeight").text = rowNode.Attributes("ows_Weight").Value

End If

If rowNode.HasAttribute("ows_ProfileNumber") = True Then

thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & i & "]/my:ProfileNumber").text = rowNode.Attributes("ows_ProfileNumber").Value

End If

If rowNode.HasAttribute("ows_InboundNumber") = True Then

thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & i & "]/my:InboundNumber").text = rowNode.Attributes("ows_InboundNumber").Value

End If

If rowNode.HasAttribute("ows_ProcessNumber") = True Then

thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & i & "]/my:ProcessNumber").text = rowNode.Attributes("ows_ProcessNumber").Value

End If

thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & i & "]/my:Material").text = rowNode.Attributes("ows_Material").Value

thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & i & "]/my:ShortNumber").text = rowNode.Attributes("ows_ID").Value

If rowNode.HasAttribute("ows_WeightTicketNumber") = True Then

thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & i & "]/my:WeighTicketNumber").text = rowNode.Attributes("ows_WeightTicketNumber").Value

End If

thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & i & "]/my:MovementType").text = CInt(0)

i = i + 1

oParentNode.appendChild(domXMLNode.documentElement.cloneNode(True))

Next

Next

Dim last As Integer = thisXDocument.DOM.selectNodes("my:myFields/my:MovementDetail").length

Dim node As IXMLDOMNode = thisXDocument.DOM.selectSingleNode("/my:myFields/my:MovementDetail[" & last & "]")

If thisXDocument.DOM.selectSingleNode("//my:myFields/my:MovementDetail[" & last & "]/my:ShortNumber").text = "" And last > 1 Then

oParentNode.removeChild(node)

End If

thisXDocument.View.EnableAutoUpdate()

End Sub





Re: XML and the .NET Framework SharePoint getListItems web service help

bart

Hi shaun,

Thanks for your post last week.

I have tried uisng infopath to retrieve the data from sharepoint list.

But how to reference that in a asp.net application...

I have no clue..

In infopath i manage to display all share point list data.

But I need to create a search function on the retrieved sharepoint data.

What I am trying ...is I retrieved all the table names from the sharepoint list to make it display in a dropdownlist.

then on selecting a paticular table name from the list , i need to display the column names under that table in the next dropdownlist.

based on those two selection i need to display the relevant data .

But i have no luck how to do this...If you have suggestion please..let me know..

cheers..

barti





Re: XML and the .NET Framework SharePoint getListItems web service help

Shaun_SPS

Theoretically, it may be possible to do this if you capture the list id with the tables. Then based on the second table selection, the list id is passed as a variable to a second web service which would return the data you want. The innate SPS web services have a getlistandview web service but I've not toyed with it yet. Once you get the data, all you need to do is use the code above to loop through and insert it into a repeating table. Good luck!

Cheers!





Re: XML and the .NET Framework SharePoint getListItems web service help

bart

Thanks very much.

I appreciate that...I am not sure that I am gonna succeed or not...

but I am just giving it a try.

Alternatively ,I can import everything in infopath and If I can integerate the infopath application in a normal .Net application ..i will be able to manipulate the data...I guess...

if you have any thoughts about it...let me know..

Thanks anyway for helping me out...

Barti..