Property Promotion with multi-level XML data type

How would you extract data from an XML datatype column when it has multiple levels? I've done this with single levels using CROSS APPLY and the nodes method, but can't seem to grab data from the intermediate levels.

Example:

<Level1>

<Level2>xyz</Level2>

<Level2>xyz</Level2>

<Level3>abc</Level3>

<Level3>abc</Level3>

<Level3>abc</Level3>

<Level2>xyz</Level2>

<Level2>xyz</Level2>

</Level1>

For the root level, I use the xml.value() function, and for the details I use the cross apply xml.nodes() function typically, but if I use the xpath in the xml.nodes, I have to use the path to the lowest level (level3) to grab those values, but can't seem to grab the changing values at the intermediate levels (level2).

Would this involve multiple cross-apply instances?

I don't understand your example, why are those Level3 elements indented further to the right than the Level2 elements? They are both children of the root element Level1.

And it is not obvious what you want to extract.

|||

Sorry if this wasn't clear. The XML is supposed to represent a multiple-level heirarchy and the indentation indicates a parent-child relationship.

So level 1 can be Order, for example. At this level there can be several elements that define the Order (ID, Cust #, etc). Level 2 would be Order Type, like Internal, External, Global, Domestic (each order always has these 4 types associated with it). Level 3 would be details relevant to that order detail item, like part #, quantity, etc. (each Order has 4 Order Types, of which have multiple line-items within each order type).

So I need to return a table of data based on this single order. The results returned would have these fields:

Order ID (repeated for every order detail)

Cust # (repeated for every order detail)

OrderType (repeated for every order type/order detail)

Part # (unique per order detail/type)

Qty (unique per order detail/type)

Analogous to joining three relational tables (Order details -> Order Type -> Order) and getting the combined results of all three.

In SQL xquery, I use the .value function to get the Order ID and Cust# because they are at the root level of the XML field. I use the CROSS APPLY .nodes method to get to the lowest level of detail (Part# and Qty) and this produces the correct data. But for the intermediate level (Order Type) I can't seem to get to it.

If it still isn't clear, I'll send some actual XML.

Thanks

Kory

|||

Consider posting the XML and the query you have, then we can work from there to improve it.

|||

A coworker of mine helped me to figure this out:

I was doing this:

Before

With Namespaces(....)
select
t.EventDateTime
,t.OrderId

,ref.value('(//gns:OrderType/text())[1]','varchar(max)') OrderType
,ref.value('(ns:Product/text())[1]','varchar(max)') Product
,ref.value('(ns:Qty/text())[1]','varchar(max)') Qty
FROM
Orders t
CROSS APPLY
xmlfld.nodes ('//ns:OrderDetailItem') AS R(ref)

And we changed it to this:

After

With Namespaces(....)
select
t.EventDateTime
,t.OrderId

,ref.value('(http://gns:OrderType/text())[1]','varchar(max)') OrderType

,ref.value('(//gns:OrderType/text())[1]','varchar(max)') OrderType
,ref.value('(ns:Product/text())[1]','varchar(max)') Product
,ref.value('(ns:Qty/text())[1]','varchar(max)') Qty
FROM
Orders t
CROSS APPLY
xmlfld.nodes ('//ns:OrderDetailItem') AS R(ref)

So the only change was to the xpath from //gnsrderType to http://gnsrderType

I really don't understand why this worked, but the first way just promoted the first occurance of the value, but the second method promoted the value when it changed.

-Kory

promotional mailing response prediction

I am trying to use the time series algorithm to predict responses to promotion mailings for subscription renewals. The problem i am having is that response is largely influenced by the number of mailings that are sent out. Can anyone give me any ideas on how i can structure the dataset so that it would take into account how many promotions were sent out? any help would be greatly appreciated.

Frank

You could have two columns in your mining model, one containing the number of mailings sent out (and used as Input) and another one containing the number of responses for the same period (used as Predict or Predict Only). You could also create a derived, calculated column, representing responses as percentage of mailings

Actually, it may be worth it to create one model with the responses modeled as Predict and another one with responses as PredictOnly

|||

Thanks, Bogdan

more detail on what I'm trying to do:

I have promotional mailings that go out periodically for subscription renewals. The goal is to predict responses to the promotion by week from the mailing date of the promotion. What I would like to do is to create a model that looks at the returns from promotions that are similar, also taking into account some other attributes(Publication,MailQty,etc).

Below is how the test data looks:

Pub Source MailQty Week1 Week2 Week3 Week4 Week5 ..................... Week20

Test Renewal 12000 45 123 234 200 34

Test2 Direct 33000 23 67 577 411 122

Test3 Renewal 22000 73 167 237 221 55

What I need to do is to use this historical response data to predict response to a future promotion. Is this possible?

Frank

|||

My guess is that you really don't want to use the time series algorithm here. You can create a dataset that uses previous response rates, calendar information and the amount of promotions mailed to predict how many responses you are going to get. For example, creating columns such as

PromotionID
DayOfWeek
WeekOfMonth
MonthOfYear
PreviousResponseRate1
PreviousReponseRate2
CurrentMailingAmount
Response

I'm sure you can think of other related variables as well - I used previous response rates in the example, but you can just as easily use absolute numbers as well. To start I would use Decision Trees (actually will create a regression tree) to predict Response, and I would also make CurrentMailingAmount a forced regressor (a parameter available in enterprise edition), to guarantee that it appears in the regression equation.

If you have the calendar attributes as numbers (which makes sense), I would also turn off the REGRESSOR flag on those columns so you don't get equations like Response = 2.45 + 0.86 * Monday

HTH

-Jamie

|||

Thanks Jamie,

Not sure if my example was as clear as it could have been. In my test data example, Week1 represents the total responses that were received 1 week from the promotion mail date. week2 represents the responses that were received the 2nd week from the mail date. i need to predict the responses by week for a future promotion by modeling past promotions. I would not have any response data for the future promotion at that point, just the other attributes such as mail quantity, day of week, monthofyear, source, etc. is this possible?

Frank

|||

Of course! This is what you are trying to predict! The example I gave is simply a sample of how you should arrange your data. For example:

(PRR = PreviousResponseRatio, CMA = CurrentMailingAmount)

PromotionID

Pub

DayOfWeek

WeekOfMonth

MonthOfYear

PRR1

PRR2

CMA

Response

P1W1

Test

4

2

3

<week 3 response/week 3 mail qty>

<week 4 response/week 4 mail qty>

<week 5 mailiing amount>

<week 5 reponse>

P1W2

Test

3

3

3

<week 4 response/week 4 mail qty>

<week 5 response/week 5 mail qty>

<week 6 mailiing amount>

<week 6 response>

In this model, one row represents a weeks mailing for a single publication. As I said before you can add additional variables as you like, such as actual previous responses, rather than a ratio - you just have to experiment.

You have other options as well. You can not have the "Pub" column at all, you could make independant models for each Pub, or you could do as above and create a model using Pub as an input. The advantage of the first option is that you could use the model to predict responses for new pubs, but you may want to add an attribute such as "TotalNumberofWeeks Sent" that describes the "newness" of a campaign.

sql

promotional mailing response prediction

I am trying to use the time series algorithm to predict responses to promotion mailings for subscription renewals. The problem i am having is that response is largely influenced by the number of mailings that are sent out. Can anyone give me any ideas on how i can structure the dataset so that it would take into account how many promotions were sent out? any help would be greatly appreciated.

Frank

You could have two columns in your mining model, one containing the number of mailings sent out (and used as Input) and another one containing the number of responses for the same period (used as Predict or Predict Only). You could also create a derived, calculated column, representing responses as percentage of mailings

Actually, it may be worth it to create one model with the responses modeled as Predict and another one with responses as PredictOnly

|||

Thanks, Bogdan

more detail on what I'm trying to do:

I have promotional mailings that go out periodically for subscription renewals. The goal is to predict responses to the promotion by week from the mailing date of the promotion. What I would like to do is to create a model that looks at the returns from promotions that are similar, also taking into account some other attributes(Publication,MailQty,etc).

Below is how the test data looks:

Pub Source MailQty Week1 Week2 Week3 Week4 Week5 ..................... Week20

Test Renewal 12000 45 123 234 200 34

Test2 Direct 33000 23 67 577 411 122

Test3 Renewal 22000 73 167 237 221 55

What I need to do is to use this historical response data to predict response to a future promotion. Is this possible?

Frank

|||

My guess is that you really don't want to use the time series algorithm here. You can create a dataset that uses previous response rates, calendar information and the amount of promotions mailed to predict how many responses you are going to get. For example, creating columns such as

PromotionID
DayOfWeek
WeekOfMonth
MonthOfYear
PreviousResponseRate1
PreviousReponseRate2
CurrentMailingAmount
Response

I'm sure you can think of other related variables as well - I used previous response rates in the example, but you can just as easily use absolute numbers as well. To start I would use Decision Trees (actually will create a regression tree) to predict Response, and I would also make CurrentMailingAmount a forced regressor (a parameter available in enterprise edition), to guarantee that it appears in the regression equation.

If you have the calendar attributes as numbers (which makes sense), I would also turn off the REGRESSOR flag on those columns so you don't get equations like Response = 2.45 + 0.86 * Monday

HTH

-Jamie

|||

Thanks Jamie,

Not sure if my example was as clear as it could have been. In my test data example, Week1 represents the total responses that were received 1 week from the promotion mail date. week2 represents the responses that were received the 2nd week from the mail date. i need to predict the responses by week for a future promotion by modeling past promotions. I would not have any response data for the future promotion at that point, just the other attributes such as mail quantity, day of week, monthofyear, source, etc. is this possible?

Frank

|||

Of course! This is what you are trying to predict! The example I gave is simply a sample of how you should arrange your data. For example:

(PRR = PreviousResponseRatio, CMA = CurrentMailingAmount)

PromotionID

Pub

DayOfWeek

WeekOfMonth

MonthOfYear

PRR1

PRR2

CMA

Response

P1W1

Test

4

2

3

<week 3 response/week 3 mail qty>

<week 4 response/week 4 mail qty>

<week 5 mailiing amount>

<week 5 reponse>

P1W2

Test

3

3

3

<week 4 response/week 4 mail qty>

<week 5 response/week 5 mail qty>

<week 6 mailiing amount>

<week 6 response>

In this model, one row represents a weeks mailing for a single publication. As I said before you can add additional variables as you like, such as actual previous responses, rather than a ratio - you just have to experiment.

You have other options as well. You can not have the "Pub" column at all, you could make independant models for each Pub, or you could do as above and create a model using Pub as an input. The advantage of the first option is that you could use the model to predict responses for new pubs, but you may want to add an attribute such as "TotalNumberofWeeks Sent" that describes the "newness" of a campaign.