2

I'm building some python programs that transfer data from SharePoint 2010 to and from a database.

Previously, I've been receiving REST queries as XML and parsing through it in order to first get the fields names and data types (like int, string, DateTime, etc...), then the actual data.

Most of the time this works fine, but it's much easier, more flexible, and much more consistent to get what I need out of it when I return it as a JSON and translate that to a python dictionary.

The only issue is that all of the values are returned as string objects. So, I was wondering if there was a way to get a FieldName: SPFieldType mapping from the REST, as a JSON.

1
+50

Hit the web (not the list) with a $metadata call.

https://not.really.mine/siteCol/Site/Web/_vti_bin/ListData.svc/$metadata

Then either cache the resulting XML and process it once for the list

-OR-

Use an XSLT transform to parse your metadata return into JSON and see if the result is close enough and quick enough. XSL 1.0 XML to JSON transform (Code below)

The result isn't super convenient to parse, but it should come out like this:

           <exerpt>
           <EntityType Name="TestCasesItem">
            <Key>
                <PropertyRef Name="Id" />
            </Key>
            <Property Name="ContentTypeID" Type="Edm.String" Nullable="true" />
            <Property Name="Title" Type="Edm.String" Nullable="true" m:FC_TargetPath="SyndicationTitle" m:FC_ContentKind="text" m:FC_KeepInContent="true" />
            <Property Name="TestingStepProcess" Type="Edm.String" Nullable="true" />
            <Property Name="ExpectedOutcome" Type="Edm.String" Nullable="true" />
            <Property Name="ActualOutcome" Type="Edm.String" Nullable="true" />
            <Property Name="Pass" Type="Edm.Boolean" Nullable="true" />
            <Property Name="CommentsIssues" Type="Edm.String" Nullable="true" />
            <Property Name="TestDate" Type="Edm.DateTime" Nullable="true" />
            <Property Name="AssignmentDate" Type="Edm.DateTime" Nullable="true" />
            <Property Name="Regression" Type="Edm.Boolean" Nullable="true" />
            <Property Name="Script" Type="Edm.Boolean" Nullable="true" />
            <Property Name="TestGroup" Type="Edm.String" Nullable="true" />
            <NavigationProperty Name="TestSuite" Relationship="Microsoft.SharePoint.DataService.TestCasesItem_TestSuite" FromRole="TestCasesItem" ToRole="TestSuite" />
            <Property Name="TestSuiteValue" Type="Edm.String" Nullable="true" />
            <NavigationProperty Name="CreatedBy" Relationship="Microsoft.SharePoint.DataService.TestCasesItem_CreatedBy" FromRole="TestCasesItem" ToRole="CreatedBy" />
            <Property Name="CreatedById" Type="Edm.Int32" Nullable="true" />
            <NavigationProperty Name="ModifiedBy" Relationship="Microsoft.SharePoint.DataService.TestCasesItem_ModifiedBy" FromRole="TestCasesItem" ToRole="ModifiedBy" />
            <Property Name="ModifiedById" Type="Edm.Int32" Nullable="true" />
            <Property Name="Modified" Type="Edm.DateTime" Nullable="true" m:FC_TargetPath="SyndicationUpdated" m:FC_ContentKind="text" m:FC_KeepInContent="true" />
            <Property Name="Created" Type="Edm.DateTime" Nullable="true" />
            <Property Name="DontTryIt" Type="Edm.String" Nullable="true" />
            <Property Name="Id" Type="Edm.Int32" Nullable="false" />
            <Property Name="ContentType" Type="Edm.String" Nullable="true" />
            <Property Name="Owshiddenversion" Type="Edm.Int32" Nullable="true" ConcurrencyMode="Fixed" />
            <Property Name="Version" Type="Edm.String" Nullable="true" />
            <NavigationProperty Name="Attachments" Relationship="Microsoft.SharePoint.DataService.TestCasesItem_Attachments" FromRole="TestCasesItem" ToRole="Attachments" />
            <Property Name="Path" Type="Edm.String" Nullable="true" />
        </EntityType>
       </exerpt Type="Malformed XML, but you get the idea"> 

Interesting XSL transform code (Credited in header)

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>

<xsl:template match="/">{
    <xsl:apply-templates select="*"/>}
</xsl:template>

<!-- Object or Element Property-->
<xsl:template match="*">
    "<xsl:value-of select="name()"/>" :<xsl:call-template name="Properties">
        <xsl:with-param name="parent" select="'Yes'"> </xsl:with-param>
    </xsl:call-template>
</xsl:template>

<!-- Array Element -->
<xsl:template match="*" mode="ArrayElement">
    <xsl:call-template name="Properties"/>
</xsl:template>

<!-- Object Properties -->
<xsl:template name="Properties">
    <xsl:param name="parent"></xsl:param>
    <xsl:variable name="childName" select="name(*[1])"/>
    <xsl:choose>            
        <xsl:when test="not(*|@*)"><xsl:choose><xsl:when test="$parent='Yes'"> <xsl:text>&quot;</xsl:text><xsl:value-of select="."/><xsl:text>&quot;</xsl:text></xsl:when>
                <xsl:otherwise>"<xsl:value-of select="name()"/>":"<xsl:value-of  select="."/>"</xsl:otherwise>
            </xsl:choose>           
        </xsl:when>                
        <xsl:when test="count(*[name()=$childName]) > 1">{ "<xsl:value-of  select="$childName"/>" :[<xsl:apply-templates select="*" mode="ArrayElement"/>] }</xsl:when>
        <xsl:otherwise>{
            <xsl:apply-templates select="@*"/>
            <xsl:apply-templates select="*"/>
            }</xsl:otherwise>
    </xsl:choose>
    <xsl:if test="following-sibling::*">,</xsl:if>
</xsl:template>

<!-- Attribute Property -->
<xsl:template match="@*">"<xsl:value-of select="name()"/>" : "<xsl:value-of select="."/>",
</xsl:template>
</xsl:stylesheet>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.