XML Data

Question

Post reply

XML Data

ikrami2000

Default port

Points: 1461
More actions
May 9, 2016 at 9:54 am

#316437

I have the below data as XML from WebRequest :
Success1<![CDATA[
Id, Name
72711, Top Banner Asset
]]>
<![CDATA[
Id, Name
4798, test123
]]>
<![CDATA[
Id, Name
72711, Media Zone
]]>
<![CDATA[
Id, Name
5411, 733ce4a0-ec1f-11e5-b6d9-715951b926b4
]]>
<![CDATA[
Id, Title, Type
4906, 13, Image
4909, 16, Image
]]>
<![CDATA[
Id, ContentId, PlayoutStart, PlayoutEnd, FullPlayout, Type, PlayerId, ApplicationId, ScheduleId, MediaZoneId, ComponentId
2664619, 4906, 3/27/2016 5:59:18 PM, 3/27/2016 5:59:30 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664620, 4906, 3/27/2016 5:59:30 PM, 3/27/2016 5:59:42 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664627, 4906, 3/27/2016 5:59:42 PM, 3/27/2016 5:59:54 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664628, 4906, 3/27/2016 5:59:54 PM, 3/27/2016 6:00:06 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664635, 4906, 3/27/2016 6:00:06 PM, 3/27/2016 6:00:18 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664636, 4906, 3/27/2016 6:00:18 PM, 3/27/2016 6:00:30 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664644, 4906, 3/27/2016 6:00:30 PM, 3/27/2016 6:00:42 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664659, 4906, 3/27/2016 6:01:21 PM, 3/27/2016 6:01:33 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664662, 4909, 3/27/2016 6:01:42 PM, 3/27/2016 6:01:54 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664663, 4906, 3/27/2016 6:01:55 PM, 3/27/2016 6:02:07 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664670, 4909, 3/27/2016 6:02:07 PM, 3/27/2016 6:02:19 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664671, 4906, 3/27/2016 6:02:20 PM, 3/27/2016 6:02:32 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664679, 4909, 3/27/2016 6:02:33 PM, 3/27/2016 6:02:45 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664680, 4906, 3/27/2016 6:02:46 PM, 3/27/2016 6:02:58 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664689, 4909, 3/27/2016 6:03:21 PM, 3/27/2016 6:03:33 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664690, 4906, 3/27/2016 6:03:34 PM, 3/27/2016 6:03:46 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664723, 4909, 3/27/2016 6:03:47 PM, 3/27/2016 6:03:59 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664724, 4906, 3/27/2016 6:04:00 PM, 3/27/2016 6:04:12 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664734, 4909, 3/27/2016 6:04:44 PM, 3/27/2016 6:04:56 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664735, 4906, 3/27/2016 6:04:57 PM, 3/27/2016 6:05:09 PM, False, Custom, 5411, 4798, 248, 72711, 72711
]]>
5001
I need to store the below part into table :
Id, ContentId, PlayoutStart, PlayoutEnd, FullPlayout, Type, PlayerId, ApplicationId, ScheduleId, MediaZoneId, ComponentId
2664619, 4906, 3/27/2016 5:59:18 PM, 3/27/2016 5:59:30 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664620, 4906, 3/27/2016 5:59:30 PM, 3/27/2016 5:59:42 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664627, 4906, 3/27/2016 5:59:42 PM, 3/27/2016 5:59:54 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664628, 4906, 3/27/2016 5:59:54 PM, 3/27/2016 6:00:06 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664635, 4906, 3/27/2016 6:00:06 PM, 3/27/2016 6:00:18 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664636, 4906, 3/27/2016 6:00:18 PM, 3/27/2016 6:00:30 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664644, 4906, 3/27/2016 6:00:30 PM, 3/27/2016 6:00:42 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664659, 4906, 3/27/2016 6:01:21 PM, 3/27/2016 6:01:33 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664662, 4909, 3/27/2016 6:01:42 PM, 3/27/2016 6:01:54 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664663, 4906, 3/27/2016 6:01:55 PM, 3/27/2016 6:02:07 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664670, 4909, 3/27/2016 6:02:07 PM, 3/27/2016 6:02:19 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664671, 4906, 3/27/2016 6:02:20 PM, 3/27/2016 6:02:32 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664679, 4909, 3/27/2016 6:02:33 PM, 3/27/2016 6:02:45 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664680, 4906, 3/27/2016 6:02:46 PM, 3/27/2016 6:02:58 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664689, 4909, 3/27/2016 6:03:21 PM, 3/27/2016 6:03:33 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664690, 4906, 3/27/2016 6:03:34 PM, 3/27/2016 6:03:46 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664723, 4909, 3/27/2016 6:03:47 PM, 3/27/2016 6:03:59 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664724, 4906, 3/27/2016 6:04:00 PM, 3/27/2016 6:04:12 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664734, 4909, 3/27/2016 6:04:44 PM, 3/27/2016 6:04:56 PM, False, Custom, 5411, 4798, 248, 72711, 72711
2664735, 4906, 3/27/2016 6:04:57 PM, 3/27/2016 6:05:09 PM, False, Custom, 5411, 4798, 248, 72711, 72711

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

OK. What do you need help with? Which technology to use? How to create a table? How to write an INSERT query?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

This should do the trick. This solution uses delimitedsplit8K which is referenced in my signature. Note my comments.

USE tempdb

GO

-- ingest the text as varchar(8000) or varchar(max) is you might receive more than 8K characters

DECLARE @x varchar(8000) =

'Success1<![CDATA[

Id, Name

72711, Top Banner Asset

]]>

<![CDATA[

Id, Name

4798, test123

]]>

<![CDATA[

Id, Name

72711, Media Zone

]]>

<![CDATA[

Id, Name

5411, 733ce4a0-ec1f-11e5-b6d9-715951b926b4

]]>

<![CDATA[

Id, Title, Type

4906, 13, Image

4909, 16, Image

]]>

<![CDATA[

Id, ContentId, PlayoutStart, PlayoutEnd, FullPlayout, Type, PlayerId, ApplicationId, ScheduleId, MediaZoneId, ComponentId

2664619, 4906, 3/27/2016 5:59:18 PM, 3/27/2016 5:59:30 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664620, 4906, 3/27/2016 5:59:30 PM, 3/27/2016 5:59:42 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664627, 4906, 3/27/2016 5:59:42 PM, 3/27/2016 5:59:54 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664628, 4906, 3/27/2016 5:59:54 PM, 3/27/2016 6:00:06 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664635, 4906, 3/27/2016 6:00:06 PM, 3/27/2016 6:00:18 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664636, 4906, 3/27/2016 6:00:18 PM, 3/27/2016 6:00:30 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664644, 4906, 3/27/2016 6:00:30 PM, 3/27/2016 6:00:42 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664659, 4906, 3/27/2016 6:01:21 PM, 3/27/2016 6:01:33 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664662, 4909, 3/27/2016 6:01:42 PM, 3/27/2016 6:01:54 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664663, 4906, 3/27/2016 6:01:55 PM, 3/27/2016 6:02:07 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664670, 4909, 3/27/2016 6:02:07 PM, 3/27/2016 6:02:19 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664671, 4906, 3/27/2016 6:02:20 PM, 3/27/2016 6:02:32 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664679, 4909, 3/27/2016 6:02:33 PM, 3/27/2016 6:02:45 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664680, 4906, 3/27/2016 6:02:46 PM, 3/27/2016 6:02:58 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664689, 4909, 3/27/2016 6:03:21 PM, 3/27/2016 6:03:33 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664690, 4906, 3/27/2016 6:03:34 PM, 3/27/2016 6:03:46 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664723, 4909, 3/27/2016 6:03:47 PM, 3/27/2016 6:03:59 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664724, 4906, 3/27/2016 6:04:00 PM, 3/27/2016 6:04:12 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664734, 4909, 3/27/2016 6:04:44 PM, 3/27/2016 6:04:56 PM, False, Custom, 5411, 4798, 248, 72711, 72711

2664735, 4906, 3/27/2016 6:04:57 PM, 3/27/2016 6:05:09 PM, False, Custom, 5411, 4798, 248, 72711, 72711

]]>

5001';

WITH

StartTxt(st) AS

(

SELECT

'Id, ContentId, PlayoutStart, PlayoutEnd, FullPlayout, Type, PlayerId, ApplicationId, ScheduleId, MediaZoneId, ComponentId'

),

Split1 AS

(

SELECT id = SUBSTRING(s1.item,1,CHARINDEX(',',s1.item)-1), s1.item

FROM StartTxt

CROSS APPLY dbo.DelimitedSplit8K(SUBSTRING(@x,CHARINDEX(st,@x)+121,8000),CHAR(10)) s1

WHERE s1.Item LIKE '%,%'

)

SELECT

id,

ContentId = MAX(CASE ItemNumber WHEN 2 THEN s2.Item END),

PlayoutStart = MAX(CASE ItemNumber WHEN 3 THEN s2.Item END),

PlayoutEnd = MAX(CASE ItemNumber WHEN 4 THEN s2.Item END),

FullPlayout = MAX(CASE ItemNumber WHEN 5 THEN s2.Item END),

[Type] = MAX(CASE ItemNumber WHEN 6 THEN s2.Item END),

PlayerId = MAX(CASE ItemNumber WHEN 7 THEN s2.Item END),

ApplicationId = MAX(CASE ItemNumber WHEN 8 THEN s2.Item END),

ScheduleId = MAX(CASE ItemNumber WHEN 9 THEN s2.Item END),

MediaZoneId = MAX(CASE ItemNumber WHEN 10 THEN s2.Item END),

ComponentId = MAX(CASE ItemNumber WHEN 11 THEN s2.Item END)

FROM Split1

CROSS APPLY DelimitedSplit8K(item,',') s2

GROUP BY id;

--ORDER BY id -- uncomment for testing/validation;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

drew.allen SSC Guru Points: 76996 More actions · Answer 3

This is not valid xml, because there is no single root element. In fact, you have text that is not contained in an element at all.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

drew.allen (5/9/2016)
This is not valid xml, because there is no single root element. In fact, you have text that is not contained in an element at all.
Drew

It's not a valid XML document (e.g. you could not save it as a .xml file and open it in an XML editor) but it's a valid XML fragment which is why this T-SQL statement does not throw and error:

DECLARE @x xml=