May 6, 2011 at 12:02 pm
I have the below query:
SELECT top 1 listing as listing
,ISNULL(category,'') as category
,ISNULL(value_proposition,'') as value_proposition
,ISNULL(value_proposition_subhead,'') as value_proposition_subhead
,ISNULL(description,'') as description
,ISNULL(short_description,'') as short_description
,ISNULL(highlights,'') as highlights
,ISNULL(reviews,'') as reviews
,ISNULL(terms,'') as terms
,ISNULL(photo_url,'') as photo_url
,ISNULL(currency,'USD') as currency
,ISNULL(value,0.00) as value
,ISNULL(price,0.00) as price
,ISNULL(starts_at,'') as starts_at
,ISNULL(ends_at,'') as ends_at
,ISNULL(expires_on,'') as expires_on
,ISNULL(facebook_title_text,'') as facebook_title_text
,ISNULL(twitter_status_text,'') as twitter_status_text
,ISNULL(featured,'') as featured
,ISNULL(upcoming,'') as upcoming
,ISNULL(enable_email_blast,'') as enable_email_blast
,ISNULL(affiliate_url,'') as affiliate_url
,ISNULL(quantity_available,'') as quantity_available
,ISNULL(min_purchase_quantity,'') as min_purchase_quantity
,ISNULL(max_purchase_quantity,'') as max_purchase_quantity
,ISNULL(location_required,'') as location_required
,ISNULL(brand_name,'') as "merchant/brand_name"
,ISNULL(logo_url,'') as "merchant/logo_url"
,ISNULL(website_url,'') as "merchant/website_url"
,ISNULL(address_line_1,'') as "merchant/locations/location/address_line_1"
,ISNULL(address_line_2,'') as "merchant/locations/location/address_line_2"
,ISNULL(city,'') as "merchant/locations/location/city"
,LTRIM(RTRIM(ISNULL(state,''))) as "merchant/locations/location/state"
,ISNULL(zip,'') as "merchant/locations/location/zip"
,ISNULL(convert(varchar(10),phone_number),'') as "merchant/locations/location/phone_number"
,ISNULL(market,'') as "markets/market"
FROM LocalDealEx.dbo.DailyDeals with (nolock)
FOR XML PATH ('daily_deal'), ROOT('import_Request'), TYPE
Results:
<import_Request>
<daily_deal>
<listing>30</listing>
<category>Sports & Recreation</category>
<value_proposition>Personal Fitness Sessions, Group Fitness Classes, A Body Blueprint, and Nutrition Guidelines!</value_proposition>
<value_proposition_subhead>$44 for One Month of Personal Training</value_proposition_subhead>
<description>Tippster, what kind of body do you want to have? While most people's answers vary—slimmer thighs, a more toned upper body, a more muscular booty, a flatter tummy—there is one thing everybody wants: a healthy, physically fit body you feel comfortable in. If you've had a hard time getting the body you want before, break out of your fitness rut with today's deal: Pay $44 for a $279 personal training and weight loss package from The Body You Want. Founder and trainer Josef Brandenburg helps regular people with hectic schedules and average genetics create the bombshell and beefcake bodies they want. Josef honed his technique while undergoing his own transformation from overweight yo-yo dieter to lean, fit workout buff. Now his staff brings this expertise to clients, drastically cutting down your weekly time spent working out, and amping up your diet for fat-loss results. You know what you want: a lean physique, increased cardiovascular health, and the confidence to wear whatever you wish. With today's high-power deal, you can find a plan that works; find out if The Body You Want is right for you with this 30-day trial membership. Train with The Body You Want to get the body of your dreams.</description>
<short_description></short_description>
<highlights><b>This fitness package includes:</b>Four personal training sessions12 group fitness classesA Body Blueprint, including a map of your body's functional movementNutrition guidelines for easy fat lossThe body you want; get one month closer to feeling great about the way you look</highlights>
<reviews></reviews>
<terms>Limit per person: 1 (+1 as a gift)Promotion Expires on August 03, 2011Must be at least 18 years or older.Limit one voucher redemption per person.</terms>
<photo_url>http://d1bseu12av3ou0.cloudfront.net/_/offer/the-body-you-want/primary</photo_url>
<currency>USD</currency>
<value>279.00</value>
<price>44.00</price>
<starts_at>2011-05-03T00:00:00</starts_at>
<ends_at>2011-05-05T00:00:00</ends_at>
<expires_on>2011-08-03T00:00:00</expires_on>
<facebook_title_text></facebook_title_text>
<twitter_status_text></twitter_status_text>
<featured>0</featured>
<upcoming>0</upcoming>
<enable_email_blast>1</enable_email_blast>
<affiliate_url>http://tippr.com/offer/the-body-you-want/</affiliate_url>
<quantity_available>0</quantity_available>
<min_purchase_quantity>1</min_purchase_quantity>
<max_purchase_quantity>0</max_purchase_quantity>
<location_required>0</location_required>
<merchant>
<brand_name>The Body You Want</brand_name>
<logo_url></logo_url>
<website_url>http://thebodyyouwant.com/</website_url>
<locations>
<location>
<address_line_1>1070 Thomas Jefferson St NW</address_line_1>
<address_line_2></address_line_2>
<city>Washington</city>
<state>DC</state>
<zip>20007</zip>
<phone_number>2023161457</phone_number>
</location>
</locations>
</merchant>
<markets>
<market>Washington</market>
</markets>
</daily_deal>
</import_Request>
This is great but I want to add:
<?xml version="1.0" encoding="UTF-8"?>
<import_request publisher_label="Jason" timestamp="20110413205005" xmlns="http://jcsql.com/api">
I know that using with XMLNamespaces with get me the xmlns part but I not sure how to get the other attributes in there (and in that exact order). I was hoping this could be done without doing alot of overhead work with temp tables, etc.
As Always on this forum, thanks for any help and insights.
May 6, 2011 at 12:53 pm
This looks like a nicely disguised ad. Doesn't belong here.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 6, 2011 at 12:55 pm
Sorry, this is ACTUAL data that I am working with and I should have definitely not used it here.
May 6, 2011 at 2:07 pm
Closer:
This works except for the --'http://JCSQL.com/api' as '@xmlns',
it gives me an error:
'xmlns' is invalid in XML tag name in FOR XML PATH, or when WITH XMLNAMESPACES is used with FOR XML.
select 'Jason' as '@publisher_label',
replace(replace(replace(convert(varchar,getdate(),120),' ',''),'-',''),':','') as '@timestamp',
--'http://JCSQL.com/api' as '@xmlns',
(
SELECT listing as listing
,ISNULL(category,'') as category
,ISNULL(value_proposition,'') as value_proposition
,ISNULL(value_proposition_subhead,'') as value_proposition_subhead
,ISNULL(description,'') as description
,ISNULL(short_description,'') as short_description
,ISNULL(highlights,'') as highlights
,ISNULL(reviews,'') as reviews
,ISNULL(terms,'') as terms
,ISNULL(photo_url,'') as photo_url
,ISNULL(currency,'USD') as currency
,ISNULL(value,0.00) as value
,ISNULL(price,0.00) as price
,ISNULL(starts_at,'') as starts_at
,ISNULL(ends_at,'') as ends_at
,ISNULL(expires_on,'') as expires_on
,ISNULL(facebook_title_text,'') as facebook_title_text
,ISNULL(twitter_status_text,'') as twitter_status_text
,ISNULL(featured,'') as featured
,ISNULL(upcoming,'') as upcoming
,ISNULL(enable_email_blast,'') as enable_email_blast
,ISNULL(affiliate_url,'') as affiliate_url
,ISNULL(quantity_available,'') as quantity_available
,ISNULL(min_purchase_quantity,'') as min_purchase_quantity
,ISNULL(max_purchase_quantity,'') as max_purchase_quantity
,ISNULL(location_required,'') as location_required
,ISNULL(brand_name,'') as "merchant/brand_name"
,ISNULL(logo_url,'') as "merchant/logo_url"
,ISNULL(website_url,'') as "merchant/website_url"
,ISNULL(address_line_1,'') as "merchant/locations/location/address_line_1"
,ISNULL(address_line_2,'') as "merchant/locations/location/address_line_2"
,ISNULL(city,'') as "merchant/locations/location/city"
,LTRIM(RTRIM(ISNULL(state,''))) as "merchant/locations/location/state"
,ISNULL(zip,'') as "merchant/locations/location/zip"
,ISNULL(convert(varchar(10),phone_number),'') as "merchant/locations/location/phone_number"
,ISNULL(market,'') as "markets/market"
FROM LocalDealEx.dbo.DailyDeals with (nolock)
FOR XML PATH ('daily_deal'), TYPE
)
FOR XML PATH ('import_Request')
May 6, 2011 at 2:28 pm
Probably not the cleanest way, but it'll get you going. I simplified the problem a bit:
DROP TABLE #tmp
GO
CREATE TABLE #tmp (somestuff VARCHAR(50))
INSERT INTO #tmp VALUES ( 'abc')
INSERT INTO #tmp VALUES ( 'def')
DECLARE @textxml VARCHAR(max)
SET @textxml = (select * from #tmp for xml raw)
print @textxml
SET @textXML = '<MoreTaggstuff>' + @textxml
print @textxml
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 9, 2011 at 11:52 am
OK....Here is how I got it working if anyone is interested. Thanks for all the help and input!
select replace('<?xml version="1.0" encoding="UTF-8" ?>' + (
select 'Jason' as '@publisher_label',
replace(replace(replace(convert(varchar,getdate(),120),' ',''),'-',''),':','') as '@timestamp',
(
SELECT listing as listing
,ISNULL(category,'') as category
,ISNULL(value_proposition,'') as value_proposition
,ISNULL(value_proposition_subhead,'') as value_proposition_subhead
,ISNULL(description,'') as description
,ISNULL(short_description,'') as short_description
,ISNULL(highlights,'') as highlights
,ISNULL(reviews,'') as reviews
,ISNULL(terms,'') as terms
,ISNULL(photo_url,'') as photo_url
,ISNULL(currency,'USD') as currency
,ISNULL(value,0.00) as value
,ISNULL(price,0.00) as price
,ISNULL(starts_at,'') as starts_at
,ISNULL(ends_at,'') as ends_at
,ISNULL(expires_on,'') as expires_on
,ISNULL(facebook_title_text,'') as facebook_title_text
,ISNULL(twitter_status_text,'') as twitter_status_text
,case when ISNULL(featured,0) = 1 then 'true' else 'false' end as featured
,case when ISNULL(upcoming,0) = 1 then 'true' else 'false' end as upcoming
,'true' as enable_email_blast
,ISNULL(affiliate_url,'') as affiliate_url
,ISNULL(quantity_available,'') as quantity_available
,ISNULL(min_purchase_quantity,'') as min_purchase_quantity
,ISNULL(max_purchase_quantity,'') as max_purchase_quantity
,case when ISNULL(location_required,0) = 1 then 'true' else 'false' end as location_required
,ISNULL(brand_name,'') as "merchant/brand_name"
,ISNULL(logo_url,'') as "merchant/logo_url"
,ISNULL(website_url,'') as "merchant/website_url"
,ISNULL(address_line_1,'') as "merchant/locations/location/address_line_1"
,ISNULL(address_line_2,'') as "merchant/locations/location/address_line_2"
,ISNULL(city,'') as "merchant/locations/location/city"
,LTRIM(RTRIM(ISNULL(state,''))) as "merchant/locations/location/state"
,ISNULL(zip,'') as "merchant/locations/location/zip"
,ISNULL(convert(varchar(10),phone_number),'') as "merchant/locations/location/phone_number"
,ISNULL(market,'') as "markets/market"
FROM DB.dbo.Jason with (nolock)
FOR XML PATH ('daily_deal'), TYPE
)
FOR XML PATH ('import_Request')),'<import_Request ','<import_Request xmlns="JCSQL/api/" ') as XMLData
May 9, 2011 at 11:53 am
and can someone tell me how to paste the code so it looks EXACTLY like I have it in T-SQL? IT always looks terrible right after I post it.
May 11, 2011 at 12:43 pm
JCSQL (5/9/2011)
and can someone tell me how to paste the code so it looks EXACTLY like I have it in T-SQL? IT always looks terrible right after I post it.
Surround your code with these:
[code="sql"]SQL CODE GOES HERE[/code]
There are IFCode shortcuts next to the input box too 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 11, 2011 at 12:45 pm
thanks....I see the light now 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply