May 20, 2014 at 8:39 am
Hello,
This is my first question here as I usually can get by with reading answers and articles from across the web, but this one has me stumped and I cannot seem to find an answer anywhere.
I have used XML Explicit because I need to output both Elements and attributes (I'll try the same later using Path but haven't reached there yet).
The query looks like:
select 1 as Tag
, NULL as Parent
, NULL as [merchant!1]
, NULL as [product!2!weboffer]
, NULL as [product!2!preorder]
, NULL as [product!2!instock]
, NULL as [product!2!forsale]
, NULL as [product!2!pid!ELEMENT]
, NULL as [product!2!name!ELEMENT]
, NULL as [product!2!desc!ELEMENT]
, NULL as [product!2!category!ELEMENT]
, NULL as [product!2!purl!ELEMENT]
, NULL as [product!2!imgurl!ELEMENT]
, NULL as [price!3!actualp!ELEMENT]
, NULL as [product!2!brand!ELEMENT]
, NULL as [product!2!currency!ELEMENT]
, NULL as [product!2!lang!ELEMENT]
, NULL as [product!2!promotext!ELEMENT]
, NULL as [product!2!spec!ELEMENT]
UNION ALL
select 2 as Tag
, 1 as Parent
, null as xx
, 'no' as weboffer
, 'no' as preorder
, 'yes' as instock
, 'yes' as forsale
, pid
, name
, [desc]
, category
, purl
, imgurl
, null as actualp
, brand
, currency
, lang
, promotext
, spec
from #CurrentData
UNION ALL
select 3 as Tag
, 2 as Parent
, null as xx
, null as weboffer
, null as preorder
, null as instock
, null as forsale
, pid
, NULL as name
, NULL as [desc]
, NULL as category
, NULL as purl
, NULL as imgurl
, Price as actualp
, null as brand
, null as currency
, null as lang
, null as promotext
, null as spec
from #CurrentData
order by [product!2!pid!ELEMENT], [price!3!actualp!ELEMENT], Tag
FOR XML EXPLICIT
It works fine and outputs correctly nested XML BUT...
It does Not match the DTD file which specifically specifies the tag Order: DTD file
It seems like the the Explicit mode reserves the right to re-arrange the tag order such that in this case the price/actualp ends up last in the output. This is perhaps some side effect of that being the only tag with a sub tag?
That means I cannot match the DTD which requires it in the specific location as written in the top query.
There does not seem to be any way to enforce the tag order using Explicit mode.
Any ideas?
Maybe I'll have more luck trying Path mode?
May 20, 2014 at 9:11 am
Here is a sample of the output. Note that the the <price> tag ends up at the end:
<merchant>
<product weboffer="no" preorder="no" instock="yes" forsale="yes">
<pid>BG:AB14:20140523:3</pid>
<name>yyyyyyy: Self Catered, Standard Apartment</name>
<desc>yyyyyyy: Self Catered, Standard Apartment, 1 Double sofa bed in lounge 1 twin 1 bedroom, Starting: 23/05/2014, Duration: 3 days</desc>
<category>Holidays</category>
<imgurl>http://yyyyyyy.com/Images/46-102405_BR-Sleep-Landing-Page-Apartments-Promo.jpg</imgurl>
<brand>yyyyyyy</brand>
<currency>GBP</currency>
<lang>EN</lang>
<promotext>Promotional text</promotext>
<spec>Room Specification</spec>
<price>
<actualp>402.00</actualp>
</price>
</product>
<product weboffer="no" preorder="no" instock="yes" forsale="yes">
<pid>BG:AB14:20140523:7</pid>
<name>yyyyyyy: Self Catered, Standard Apartment</name>
<desc>yyyyyyy: Self Catered, Standard Apartment, 1 Double sofa bed in lounge 1 twin 1 bedroom, Starting: 23/05/2014, Duration: 7 days</desc>
<category>Holidays</category>
<imgurl>http://yyyyyyy.com/Images/46-102405_BR-Sleep-Landing-Page-Apartments-Promo.jpg</imgurl>
<brand>yyyyyyy</brand>
<currency>GBP</currency>
<lang>EN</lang>
<promotext>Promotional text</promotext>
<spec>Room Specification</spec>
<price>
<actualp>751.00</actualp>
</price>
</product>
</merchant>
May 20, 2014 at 12:57 pm
Hi. Yes path mode will give you a lot more control over the attributes and elements. You can specify the path of nodes as part of the column alias. Pretty cool stuff.
The query below is adapted from your example
SELECT weboffer '@weboffer'
, preorder '@preorder'
, instock '@instock'
, forsale '@forsale'
, pid
, name
, [desc]
, category
, purl
, imgurl
, actualp 'price/actualp'
, brand
, currency
, lang
, promotext
, spec
FROM #Currentdata
FOR XML PATH('product'), ROOT('merchant')
Hope that helps.
May 20, 2014 at 1:05 pm
In fact, i've just seen your order by clause.. That is why the tag is being moved.
In any case, I generally avoid using EXPLICIT unless i have to generate an actual CDATA section in the xml as the syntax for EXPLICIT is harder and less readable than using PATH/RAW/AUTO etc.
May 21, 2014 at 3:15 am
Thanks, that is exactly what I did in the end. Not having tried Path mode before (basically following a tutorial in the order they explained things :blush: ) I finally gave it a go and it was ultimately Much simpler.
What did you mean by the sort order being a problem? I thought that only affected the parsing of the underlying data set, not the order of the fields?
May 21, 2014 at 3:55 am
What did you mean by the sort order being a problem? I thought that only affected the parsing of the underlying data set, not the order of the fields?
Sorry, ignore this, you are correct. I must have been thinking about something else and didn't validate my response to you.
May 21, 2014 at 4:03 am
No worries 😉
I'm a happy bunny now after having actually Validated the code too. It turned out that apart from the obvious original error a number of other fields were in the wrong order according to the DTD but now all sorted.
Thanks for all the support, much appreciated! 😎
May 21, 2014 at 4:07 am
No problem.. thanks for the feedback! 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
