?
Solved

Export SharePoint Data as XML

Posted on 2006-05-04
8
Medium Priority
?
7,955 Views
Last Modified: 2013-11-19
I need help creating a script to pull specific data from a MS SharePoint list when requested via HTTP (Msxml2.ServerXMLHTTP).

I do not think I can use owssvr.dll directly, because the list contains sensitive information that I do NOT want to be accessible.  I would also like to add XML information such as the date of the request.

A web server will request the XML data from a remote server running SharePoint.  The web server will then filter and sort the XML records.

I have MS FrontPage 2003, if that makes it easier.

Thank you for your advice!
0
Comment
Question by:llessurt
  • 5
  • 3
8 Comments
 
LVL 26

Expert Comment

by:rdcpro
ID: 16614850
You have number of possibilities, with SharePoint.  Since the current version of SharePoint doesn't do any security trimming (though SharePoint 2007 does), your best bet is to use the SharePoint web service for this.  I'm at home at the moment, but when I get to work, I'll post more info.

I use SharePoint web services quite a bit--they're very handy, and pretty easy to use.  You could use the Search service, which allows you to select the data from the list that you want, but there is a list service as well.  If I recall correctly, you can do almost anything you want via the web service, except create a new list...

In any case, I'll post an example when I get to work in a few hours.  Do you also have Visual Studio 2003, and is the client server (the one you're say you're going to use MSXML with) running ASP.NET?

Regards,
Mike Sharp
0
 
LVL 3

Author Comment

by:llessurt
ID: 16619203
Please illustrate how to implement the SharePoint web service.

The client server is running ASP.NET -- I'm planning to save the XML response as a file so it can be accessed directly from the client.  The advice a need is how to compile the XML from the SharePoint list to send to the client.

I do not have Visual Studio.

Thank You
0
 
LVL 3

Author Comment

by:llessurt
ID: 16630732
Is there a better category to make this post?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 26

Accepted Solution

by:
rdcpro earned 2000 total points
ID: 16634514
Sorry, I've been swamped, and then didn't go online over the weekend.

I'm mostly familiar with how to do this using Visual Studio, and it automatically generates the proxy code you'll need to interact with the web service.  I think you can use FrontPage for this...but I rarely use it, so I can't say for sure.  You can always write your code in Notepad, and compile it using CSC, of course.  But that's the hard way.  This Blog outlines a way to get List data from the SharePoint List service using FrontPage, but make sure that your data is security trimmed...as I said, I rarely use FrontPage, so I can't swear that this will meet your needs:

http://blogs.msdn.com/frontpoint/archive/2004/10/21/245699.aspx

You can always construct a SOAP message manually in using MSXML's DomDocument object, then POST the message to the web service endpoint using Msxml2.ServerXMLHTTP (as you mentioned in your first post).  Or you can download the SOAP toolkit, and use the wsdl.exe to generate the proxy for you.  


Assuming you had a web reference (say, from Visual Studio, or from the proxy generated by the SOAP toolkit), this is how you'd get data from a list (in C#, from the WSS SDK):


Web_Reference_Folder_Name.Lists listService = new Web_Reference_Folder_Name.Lists();
listService.Credentials= System.Net.CredentialCache.DefaultCredentials;

XmlDocument xmlDoc = new System.Xml.XmlDocument();

XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element,"Query","");
XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element,"ViewFields","");
XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element,"QueryOptions","");

ndQueryOptions.InnerXml = "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>" +
    "<DateInUtc>TRUE</DateInUtc>";
ndViewFields.InnerXml = "<FieldRef Name='Field1' /><FieldRef Name='Field2'/>";
ndQuery.InnerXml = "<Where><And><Gt><FieldRef Name='Field1'/>" +
    "<Value Type='Number'>5000</Value></Gt><Gt><FieldRef Name='Field2'/>" +
    "<Value Type='DateTime'>2003-07-03T00:00:00</Value></Gt></And></Where>";
try
{
    XmlNode ndListItems = listService.GetListItems("List_Name", null, ndQuery,
        ndViewFields, null, ndQueryOptions);
    MessageBox.Show(ndListItems.OuterXml);
}

catch (System.Web.Services.Protocols.SoapException ex)
{
    MessageBox.Show("Message:\n" + ex.Message + "\nDetail:\n" + ex.Detail.InnerText +
         "\nStackTrace:\n" + ex.StackTrace);
}



Here are a few URLs to get you started:

The Windows SharePoint Services SDK has a lot of info to start:

    -    http://www.microsoft.com/downloads/details.aspx?familyid=1c64af62-c2e9-4ca3-a2a0-7d4319980011&displaylang=en

You can always browse directly to the SharePoint web service endpoint, and see what nice things it has to offer.  For example, on one of my dev servers where I have SharePoint running, I can go to this URL:

    -    http://dev01wpssearch/_vti_bin/lists.asmx

This shows me all operations on lists that SharePoint supports via this web service.  In particular, you'd be interested in the GetListItems() method:

    -    http://dev01wpssearch/_vti_bin/lists.asmx?op=GetListItems

The SOAP message looks like:

POST /_vti_bin/lists.asmx HTTP/1.1
Host: dev01wpssearch
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://schemas.microsoft.com/sharepoint/soap/GetListItems"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
      <listName>string</listName>
      <viewName>string</viewName>
      <query>
        <xsd:schema>schema</xsd:schema>xml</query>
      <viewFields>
        <xsd:schema>schema</xsd:schema>xml</viewFields>
      <rowLimit>string</rowLimit>
      <queryOptions>
        <xsd:schema>schema</xsd:schema>xml</queryOptions>
    </GetListItems>
  </soap:Body>
</soap:Envelope>

Here's the SDK info for the GetListItems method.  As you can see, you can specify a ViewName

GetListItems Method

The GetListItems method of the Lists service returns information about items in the list based on the specified query.


Parameters


listName   A string that contains either the title or the GUID for the list. When querying the UserInfo table, the string contains "UserInfo".



viewName   A string that contains the GUID for the view, which determines the view to use for the default view attributes represented by the query, viewFields, and rowLimit parameters. If this argument is not supplied, the default view is assumed. If it is supplied, the value of the query, viewFields, or rowLimit parameter overrides the equivalent setting within the view. For example, if the view specified by the viewFields parameter has a row limit of 100 rows but the rowLimit parameter contains 1000, then 1,000 rows will be returned in the response.



query   A Query element containing the query that determines which records are returned and in what order and that can be assigned to a System.Xml.XmlNode object, as in the following example:

<Query>
   <Where>
      <Lt>
         <FieldRef Name="ID" />
         <Value Type="Counter">3</Value>
      </Lt>
   </Where>
</Query>
If supplied, the value of this parameter overrides either the query within the view specified by the viewName parameter or the query within the default view for the list.



viewFields   A ViewFields element that specifies which fields to return in the query and in what order, and that can be assigned to a System.Xml.XmlNode object, as in the following example:

<ViewFields>
   <FieldRef Name="ID" />
   <FieldRef Name="Title" />
</ViewFields>
If supplied, the value of this parameter overrides the view fields within the view specified by the viewName parameter or the view fields in the default view for the list.



rowLimit   A string that specifies the number of items, or rows, to display on a page before paging begins. If supplied, the value of this parameter overrides the row limit set in the view specified by the viewName parameter or the row limit set in the default view for the list.



queryOptions   An XML fragment in the following form that contains separate nodes for the various properties of the SPQuery object and that can be assigned to a System.Xml.XmlNode object:

<QueryOptions>
   <IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>
   <DateInUtc>TRUE</DateInUtc>
</QueryOptions>


This article shows how to get the list from SharePoint, and convert it into a DataSet.  This might be convenient if you need to end up with a DataSet for some reason.

    -    http://www.developer.com/tech/article.php/3104621


Regards,
Mike Sharp
0
 
LVL 3

Author Comment

by:llessurt
ID: 16648369
Thanks rdcpro, I'm still looking at your solution -- this process may not be as straightforward as I was hoping.  I am lacking in experience I guess.

FrontPage gives me a data connection very easily, but wants to format it in a table with with menus and so many on a page, etc.

Basically I want to open a SharePoint list, specify a query and the fields I want, add a the current date, and send the whole thing to the remote web server as an XML document.  The web server will save the document and access the saved version locally.

It will be accessible remotely, so it is important that only the specified fields with the query can be accessed (exported).

Am I going about this the wrong way?  With these methods, it looks like I will have to manually build the XML document (and I haven't been successful).
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 16653978
I wish I was more experienced with FrontPage.  No, wait, I take that back.  ;^)

Actually, it may not be possible at all, if FrontPage doesn't allow you to write any code.  But the DataSet doesn't have to be bound to a datagrid for display, and you should be able to get the XML directly from the DataSet.

Otherwise, you'll have to use the Web Service directly, and construct the web client yourself, assuming you can do this in FrontPage.  

You can always do this in code and compile from the command line, too.  Visual Studio is nice, but it's a luxury, not a requirement.  But I'd say development in Notepad and command line compilation is not for the faint of heart!

Regards,
Mike Sharp
0
 
LVL 3

Author Comment

by:llessurt
ID: 16749426
I appreciate all your help.  I would repost, but I'm not sure where this question should go.  I downloaded Visual Studio Express 2005, but I have no idea what to do with it.

I'm looking for something very simple....  I know this is a mess, but it illustrates what I am looking for.

<%@ Page Language="VB" %>
<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls"
      Assembly="Microsoft.SharePoint, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities"
      Assembly="Microsoft.SharePoint, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Import Namespace="Microsoft.Sharepoint" %>

<script runat="server">

    Public Class SPWeb
        Inherits System.Object
    End Class
   
    Sub Page_Load()

        Dim mySite As SPWeb = SPControl.GetContextWeb(Context)
        Dim list As SPList = mySite.Lists("ATS Job Orders")

        Dim query As New SPQuery()
            query.Query = "<Where><Eq><FieldRef Name="Status"/><Value Type="x:string">In Print</Value></Eq></Where><OrderBy><OrderField Name="Created" Type="xsd:string" Direction="DESC"/><OrderField Name="Order_x0020__x0023_" Type="xsd:string" Direction="DESC"/></OrderBy>"

        Dim myItems As SPListItemCollection = list.GetItems(query)
        Dim item As SPListItem
        Response.Write("<xml><books>")
        For Each item In myItems
            Response.Write("<book ID" & CHR(34) item("id").ToString() & CHR(34) & "><title>" & item("Title").ToString() & "</title></book>")
        Next item
       
        Dim list As SPList = mySite.Lists("Staff")
        Dim query As New SPQuery()
query.Query = "<Where><Eq><FieldRef Name="Status"/><Value Type="x:string">Open</Value></Eq></Where><OrderBy><OrderField Name="Created" Type="xsd:string" Direction="DESC"/><OrderField Name="Order_x0020__x0023_" Type="xsd:string" Direction="DESC"/></OrderBy>"

        Dim myItems As SPListItemCollection = list.GetItems(query)
        Dim item As SPListItem
        Response.Write("</books><authors>")
         For Each item In myItems
            Response.Write("<name>" & item("Name").ToString() & "</name>")
        Next item
        Response.Write("</authors></xml">)
    End Sub
</script>


This ASP page retreives the data:

<%
Option Explicit
On Error Resume Next
Response.Buffer = True

Dim xml, fso, xmldom, objNodeList

Set xml = Server.CreateObject("Msxml2.ServerXMLHTTP")
xml.Open "POST", "https://www.remoteserver.com/customexport.aspx", False
xml.Send

      Set fso = Server.CreateObject("scripting.FileSystemObject")
      Set datafile = fso.CreateTextFile("c:\booksauthors.xml", true)
      datafile.write xml.responseText
      Set datafile = Nothing
      Set fso = Nothing

Set xml = Nothing

response.redirect newpage
%>
0
 
LVL 3

Author Comment

by:llessurt
ID: 16803847
I never did get SharePoint WebService to do what I wanted it to do.  I ended up using a table generated by MS-FrontPage (very easy to create with a few clicks), requesting it via HTTP (Msxml2.ServerXMLHTTP, and manipulating it as text to pull out the data and create an XML file.

I very much appreciate your help and suggestions.

Thank You!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question