April 9, 2010 at 8:00 am
I have a for xml statemnet to produce some xml out put, the issue i'm having is that the schema dictates that one of the element names is dynamic based on a card type, as you can see from the code below, the elements <ECMC-SSL> and <AMEX-SSL> are dynamic based on the cardnumber element, can anyone help me with some xquery to modify the code to change the element name based on the first digit of the card number?
At the moment my for xml statement just hard codes a tag called ToChange, here's an extract of that statement.
i'd be so grateful for any help as this is driving me nuts1
Thanks
SELECT NULL,
(
SELECT cardNumber,
(
SELECT Month,Year
FROM XMLTransactions
FOR XML RAW('Date'),TYPE, ROOT('Expirydate')
),
(
SELECT cardHolderName
FROM XMLTransactions
FOR XML RAW(''),ELEMENTS,TYPE
)
FOR XML PATH(''),TYPE
)
FOR XML RAW('ToChange),ELEMENTS,TYPE
- <order orderCode="141324259">
<description>MOBILE PHONE USAGE</description>
<amount value="14268" currencyCode="GBP" exponent="2" debitCreditIndicator="credit" />
<orderContent>37482</orderContent>
- <paymentDetails action="AUTHORISE">
- <ECMC-SSL>
<cardNumber>5434**********</cardNumber>
- <expiryDate>
<date month="04" year="2010" />
</expiryDate>
<cardHolderName>MR TEST CUSTOMER</cardHolderName>
</ECMC-SSL>
</paymentDetails>
</order>
- <order orderCode="141324300">
<description>MOBILE PHONE USAGE</description>
<amount value="8323" currencyCode="GBP" exponent="2" debitCreditIndicator="credit" />
<orderContent>37482</orderContent>
- <paymentDetails action="AUTHORISE">
- <AMEX-SSL>
<cardNumber>3763*********</cardNumber>
- <expiryDate>
<date month="04" year="2010" />
</expiryDate>
<cardHolderName>ROBERT TEST</cardHolderName>
</AMEX-SSL>
</paymentDetails>
</order>
April 9, 2010 at 11:33 am
Please provide table def and sample data that correspond to your expected result in a ready to use format as described in the first link in my signature.
April 12, 2010 at 6:18 am
lmu92 (4/9/2010)
Please provide table def and sample data that correspond to your expected result in a ready to use format as described in the first link in my signature.
Thanks Lutz your link was most helpful, here's your requested details, hope you can help
Table def
CREATE TABLE XMLTransactions(
OrderCode VARCHAR(20),
Description VARCHAR(50),
Value INT,
CurrencyCode VARCHAR(3),
Exponent INT,
debitCreditIndicator VARCHAR(20),
ordercontent VARCHAR(20),
Action VARCHAR(20),
CardNumber VARCHAR(32),
Month VARCHAR(2),
Year INT,
CardHolderName VARCHAR(50)
)
Basically what i need to achieve is a dynamic element name, the one in bold below.
The name of the element will depend on the first digit of the card number element, so for example, a card number starting with 3 would mean that the dynamic element should be called <AMEX-SSL> Whilst a card number starting in 5 would require that the element name be <ECMC-SSL>
I cannot figure out a way of doing this in the for xml statement its self, but I'm sure there must be a way to do it with xquery or something i just can't seem to work it out.
I really appreciate your reply, thanks
- <order orderCode="141324259">
<description>MOBILE PHONE USAGE</description>
<amount value="14268" currencyCode="GBP" exponent="2" debitCreditIndicator="credit" />
<orderContent>37482</orderContent>
- <paymentDetails action="AUTHORISE">
- <ECMC-SSL>
<cardNumber>5434789654123654</cardNumber>
- <expiryDate>
<date month="04" year="2010" />
</expiryDate>
<cardHolderName>MR TEST CUSTOMER</cardHolderName>
</ECMC-SSL>
</paymentDetails>
</order>
- <order orderCode="141324300">
<description>MOBILE PHONE USAGE</description>
<amount value="8323" currencyCode="GBP" exponent="2" debitCreditIndicator="credit" />
<orderContent>37482</orderContent>
- <paymentDetails action="AUTHORISE">
- <AMEX-SSL>
<cardNumber>376344444444444</cardNumber>
- <expiryDate>
<date month="04" year="2010" />
</expiryDate>
<cardHolderName>ROBERT TEST</cardHolderName>
</AMEX-SSL>
</paymentDetails>
</order
April 12, 2010 at 10:33 am
Okay, we have the table def. But where are the INSERT statements?
You posted the expected result the second time... 😉
April 13, 2010 at 4:57 am
lmu92 (4/12/2010)
Okay, we have the table def. But where are the INSERT statements?You posted the expected result the second time... 😉
Sorry Lutz, here's the insert
INSERT XMLTransactions (
OrderCode,
Description,
Value,
CurrencyCode,
Exponent,
debitCreditIndicator,
ordercontent,
Action,
CardNumber,
Month,
Year,
CardHolderName
)
VALUES (
'141323364',
'MOBILE PHONE USAGE',
1999,
'GBP',
2,
'credit',
'37482',
'AUTHORISE',
'376344444444444',
'05',
2010,
'MISS TEST TEST'
)
Appreciate your patcience, i'm new to these forumns.....
Thanks a million
April 13, 2010 at 12:41 pm
I'm not sure if that's exactly what you're looking for... But at least it should give you something to start with.
The tricky part to get different node names is to use a CASE condition and benefit from the xml ignoring empty nodes.
I'm pretty sure this is not going to perform that well since it will touch the XMLTransactions seven times to build the xml structure. So it's important to have proper indexing to support this query.
Furthermore, I'm not sure if my WHERE condition is correct. You need to verify and test. (Which is true for the whole code anyway... 😉 )
SELECT
orderCode '@orderCode',
description,
(
SELECT
Value '@Value',
CurrencyCode '@CurrencyCode',
Exponent '@Exponent',
debitCreditIndicator '@debitCreditIndicator'
FROM XMLTransactions xSSL2
WHERE xSSL3.orderCode = xSSL2.orderCodeFOR XML PATH('amount'),TYPE
),
ordercontent,
(SELECT
ACTION AS '@action',
CASE WHEN cardNumber LIKE '5%'
THEN
(SELECT
cardnumber,
(SELECT
xExpD.month AS '@month',
xExpD.year AS '@year'
FROM XMLTransactions xExpD
WHERE xExpD.orderCode = xSSL.orderCode FOR XML PATH('date'), TYPE
),
CardHolderName
FROM XMLTransactions xSSL
WHERE xSSL2.orderCode = xSSL.orderCode FOR XML PATH('EMC-SSL'), TYPE
)
END ,
CASE WHEN cardNumber LIKE '3%'
THEN
(SELECT
cardnumber,
(SELECT
xExpD.month AS '@month',
xExpD.year AS '@year'
FROM XMLTransactions xExpD
WHERE xExpD.orderCode = xSSL.orderCode FOR XML PATH('date'), TYPE
),
CardHolderName
FROM XMLTransactions xSSL
WHERE xSSL2.orderCode = xSSL.orderCode FOR XML PATH('AMEX-SSL'), TYPE
)
END
FROM XMLTransactions xSSL2
WHERE xSSL3.orderCode = xSSL2.orderCode FOR XML PATH('paymentDetails'),TYPE
)
FROM XMLTransactions xSSL3 FOR XML PATH('order')
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply