Bulk load, transform problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Richard R (rrose_at_informsnospam.co.uk)
Date: 05/10/04 

Date: Mon, 10 May 2004 15:18:31 +0100

Hi All,

I have to retrieve some information that I have received in XML, and convert
it to a simple table in SQL.
I am using the SQLXMLBulkLoad object in a DTS package, and while I can get
the samples working I am really
struggling to get the mapping for my XML document, which is somewhat more
complex than their
examples.
In particular I don't understand how to map down several nested elements to
retrieve the one item
I actaully need. I thought I did...

If anyone can show me what the mapping xml file should be I would be very
grateful indeed., as the alternative is to manually re-type a few hundred of
these.

Details of the input XML and required output rowset are given below.

Thanks in advance,

Richard 

---
The XML represents part of ProClarity briefing book (not that that matters).
Within this there are several calculated members (Element
<CalculatedMember>)
each of which is composed of multiple Members (Element <Member>.  )
This effectively encodes a one-to-many relationship, which is what I want to
retrieve into a SQL table.
The XML is as follows:
<BriefingPage3>
 <ConnectionInfo>
  <Provider Name="{a07ccd0c-8148-11d0-87bb-00c04fc33942}" Caption="MSOLAP"/>
  <Server Name="ERIC"/>
  <Catalog Name="Catalog1"/>
  <Cube Name="[Cube1]" Caption="Cube1G"/>
  <Schema Name=""/>
  </ConnectionInfo>
 <Commands>
  <Command UsingDecoratedNames="Yes" SlicerConversion="No" Active="DSH">
   <CommandSettings/>
   <MDX> A great big string of MDX in here </MDX>
   <DDLObjects>
    <CalculatedMember Version="1" Name="[&lt;##&lt;MEMBER!RichardR!A M
Searle&gt;##&gt;]"    ...  />
     <DDLObjects UsingDecoratedNames="Yes"/>
     <MemberSet>
      <Member>
       <Member Name="[Hauliers].[Standard
Name].&amp;[SEARLES].&amp;[SEARLES].&amp;[SEARL]" MDID="35"/>
       </Member>
      <Member>
       <Member Name="[Hauliers].[Standard
Name].&amp;[SEARLES].&amp;[SEARLES].&amp;[SEARL AM]" MDID="36"/>
       </Member>
      <Member>
       <Member Name="[Hauliers].[Standard
Name].&amp;[SEARLES].&amp;[SEARLES].&amp;[SEARL A]" MDID="37"/>
       </Member>
....
What I want to retrieve from this is a table with the Name attribute of the
CalculatedMember element, and the
Name and MDID attributes of the lower Member element:
Name
MemberName
MDID
-----                                                                       
                ----------                                                  
                                                                       ----
[&lt;##&lt;MEMBER!RichardR!A M Searle&gt;##&gt;]     [Hauliers].[Standard
Name].&amp;[SEARLES].&amp;[SEARLES].&amp;[SEARL]         36
[&lt;##&lt;MEMBER!RichardR!A M Searle&gt;##&gt;]     [Hauliers].[Standard
Name].&amp;[SEARLES].&amp;[SEARLES].&amp;[SEARL AM]  37
[&lt;##&lt;MEMBER!RichardR!A M Searle&gt;##&gt;]    [Hauliers].[Standard
Name].&amp;[SEARLES].&amp;[SEARLES].&amp;[SEARL A]       38
>From here I can get to what I actually want, which is
Name                 MemberName     MDID
----                      -----------           -------
A M Searle     SEARL                    36
A M Searle     SEARL    AM           37
A M Searle     SEARL A                38

Relevant Pages

  • RE: DataSet Tree
    ... It is possible to shape your XML from SQL and retrieve it with ADO.NET. ... Here is a very small sample using the northwind database: ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Where are the list items stored?
    ... they are in the database but it is rare you ever need to retrieve them from there. ... Would you like to retrieve list items in XML? ... I'm wondering where I can find the list items in WSS? ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Script to populate Distribution list
    ... ' Check if the user is already a member of the group. ... To retrieve the selected attribute for all users, ... You loop through the recordset. ... ' Filter to retrieve only user objects. ...
    (microsoft.public.scripting.vbscript)
  • Re: Script to search for a Users OU in Active Directory
    ... Can you suggest some website,reference to understand these scripting ... > user is a member of a group. ... you can retrieve OU info in several ways: ... > efficient to use the NameTranslate object. ...
    (microsoft.public.windows.server.scripting)
  • Re: Error message: During a logon attempt, the users security context
    ... it even shows: MaxTokenSize: 11393). ... your problem is that you cannot retrieve more than 1000 values ... If you query AD for all members of a large group, you may get more than 1000 ... users is a member of, you should get one record with an array of values. ...
    (microsoft.public.windows.server.active_directory)