Extracting data from xml

Question

Extracting data from xml

ayemya

Hall of Fame

Points: 3495
More actions
May 22, 2013 at 10:13 am

#98590

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>
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Unfortunately, unless you mangled that XML while posting, you will have to use string processing on that as it is not valid XML.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ayemya Hall of Fame Points: 3495 More actions · Answer 2

ayemya

Hall of Fame

Points: 3495

May 23, 2013 at 7:00 am

#1617844

Thanks.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

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

SELECTc.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

*/

SELECTCity = 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 (

SELECTChildFirstName = 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')

FROMArrayOfEntry.nodes('(Entry[DataType = "Session:Childrens"]/Child)') p2(Child)

) c