Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extracting data from xml Expand / Collapse
Author
Message
Posted Wednesday, May 22, 2013 10:13 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:42 PM
Points: 118, Visits: 875
I am trying to read data from the following sample XML file. I have tried SSIS XML task to remove namespaces. I had error after I use xml source to read data. It errors SSIS does not support mixed content.

Is there another way to get data from the following xml sample? Thank you.

<CompoundLogData>
<ArrayOfEntry>
<Entry>
<DataType>Session:City</DataType>
<Value type="xsd:string">Glen Allen</Value>
</Entry>
<Entry>
<DataType>Session:State</DataType>
<Value type="xsd:string">VA</Value>
</Entry>
<Entry>
<DataType>Session:ZipCode</DataType>
<Value type="xsd:string">74115</Value>
</Entry>
<Entry>
<DataType>Session:Childrens</DataType>
<Value type="q1:AddEditChildViewModel">
<Child>
<ChildFirstName>ABC</ChildFirstName>
<ChildLastName>TTT</ChildLastName>
<BirthMonth>5</BirthMonth>
<BirthYear>1999</BirthYear>
<MemberId>0004554</MemberId>
<IsRemoved>false</IsRemoved>
<HasFreeMembership>true</HasFreeMembership>
</Child>
</entry>
</CompoundLogData>
</ArrayOfEntry>
Post #1455598
Posted Wednesday, May 22, 2013 12:51 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:38 PM
Points: 1,805, Visits: 5,870
Unfortunately, unless you mangled that XML while posting, you will have to use string processing on that as it is not valid XML.

MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1455653
    Posted Thursday, May 23, 2013 7:00 AM


    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Friday, November 14, 2014 12:42 PM
    Points: 118, Visits: 875
    Thanks.
    Post #1455991
    Posted Thursday, May 30, 2013 1:52 PM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Tuesday, November 4, 2014 1:50 PM
    Points: 2,845, Visits: 1,160
    After turning your sample into valid XML, I was able to query it with T-SQL

    DECLARE @xml XML = '
    <CompoundLogData>
    <ArrayOfEntry>
    <Entry>
    <DataType>Session:City</DataType>
    <Value type="xsd:string">Glen Allen</Value>
    </Entry>
    <Entry>
    <DataType>Session:State</DataType>
    <Value type="xsd:string">VA</Value>
    </Entry>
    <Entry>
    <DataType>Session:ZipCode</DataType>
    <Value type="xsd:string">74115</Value>
    </Entry>
    <Entry>
    <DataType>Session:Childrens</DataType>
    <Value type="q1:AddEditChildViewModel"/>
    <Child>
    <ChildFirstName>ABC</ChildFirstName>
    <ChildLastName>TTT</ChildLastName>
    <BirthMonth>5</BirthMonth>
    <BirthYear>1999</BirthYear>
    <MemberId>0004554</MemberId>
    <IsRemoved>false</IsRemoved>
    <HasFreeMembership>true</HasFreeMembership>
    </Child>
    </Entry>
    </ArrayOfEntry>
    </CompoundLogData>';

    -- Look at the individual "Entry" nodes
    SELECT c.query('.')
    FROM @xml.nodes('CompoundLogData/ArrayOfEntry/Entry') T(c)

    /* Retrieve all columns, based on assumptions about the sample XML
    There will be one or more ArrayOfEntry nodes
    Only the first Entry node for City, State, and ZipCode will be shown for each ArrayOfEntry
    The cardinality of the "Session:Childrens" nodes is not specified, so this assumes it is 0, 1, or many
    The Value/@type attributes are not used, assuming they are not necessary
    */
    SELECT City = ArrayOfEntry.value('(Entry[DataType = "Session:City"]/Value)[1]', 'varchar(50)'),
    State = ArrayOfEntry.value('(Entry[DataType = "Session:State"]/Value)[1]', 'char(2)'),
    ZipCode = ArrayOfEntry.value('(Entry[DataType = "Session:ZipCode"]/Value)[1]', 'varchar(15)'),
    c.*
    FROM @xml.nodes('CompoundLogData/ArrayOfEntry') T(ArrayOfEntry)
    OUTER APPLY (
    SELECT ChildFirstName = Child.value('ChildFirstName[1]', 'varchar(50)'),
    ChildLastName = Child.value('ChildLastName[1]', 'varchar(50)'),
    BirthMonth = Child.value('BirthMonth[1]', 'tinyint'),
    BirthYear = Child.value('BirthYear[1]', 'smallint'),
    MemberId = Child.value('MemberId[1]', 'varchar(50)'),
    IsRemoved = Child.value('IsRemoved[1]', 'bit'),
    HasFreeMembership = Child.value('HasFreeMembership[1]', 'bit')
    FROM ArrayOfEntry.nodes('(Entry[DataType = "Session:Childrens"]/Child)') p2(Child)
    ) c




    Post #1458428
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse