FOR XML EXPLICIT - Wrong Element Order

Question

Post reply

FOR XML EXPLICIT - Wrong Element Order

rstone

SSCertifiable

Points: 6213
More actions
December 29, 2004 at 6:49 pm

#60903

Can anybody help me get this right? I have been trying to generate an XML document that looks like the following sample below (just part of a larger document). Notice how the <j:LocationStreet> contained elements come before the <j:LocationSecondaryUnitText> element - this is required. When I write my FOR XML EXPLICIT query, my sequence is not correct. How can I change the sequence to what I want? I don't want to have extra or repeated <j:LocationAddress> or <j:LocationStreet> elements for a single record - I get these so far when I try to be creative and get the right order. The repeated tags either are empty or contain the elements following <j:LocationStreet>. I'm almost ready to hard code this.
Desired Sample Result:
<j:Residence j:sourceIDText="667788">
<j:LocationTypeText>Residence</j:LocationTypeText>
<j:LocationTypeCode>20</j:LocationTypeCode>
<j:LocationAddress>
  <j:LocationStreet>
   <j:StreetFullText>123 N San Luis Way Apt c</j:StreetFullText>
   <j:StreetNumberText>123</j:StreetNumberText>
   <j:StreetPredirectionalText>N</j:StreetPredirectionalText>
   <j:StreetName>San Luis Way</j:StreetName>
   <j:StreetTypeText>Boulevard</j:StreetTypeText>
  </j:LocationStreet>
  <j:LocationSecondaryUnitText>113C</j:LocationSecondaryUnitText>
  <j:LocationCityName>Novato</j:LocationCityName>
  <j:LocationStateCode.USPostalService>CA</j:LocationStateCode.USPostalService>
  <j:LocationPostalCodeID>
   <j:ID>94949</j:ID>
  </j:LocationPostalCodeID>
  <j:LocationPostalCodeExtensionID>
   <j:ID>1234</j:ID>
  </j:LocationPostalCodeExtensionID>
  <j:LocationCountryCode.iso3166Alpha3>USA</j:LocationCountryCode.iso3166Alpha3>
</j:LocationAddress>
</j:Residence>
This is what I get:
<j:Residence j:sourceIDText="55878">
<j:LocationTypeText>Residence</j:LocationTypeText>
<j:LocationTypeCode>20</j:LocationTypeCode>
<j:LocationAddress>
  <j:LocationSecondaryUnitText></j:LocationSecondaryUnitText>
  <j:LocationCityName>Novato</j:LocationCityName>
  <j:LocationStateCode.USPostalService>CA</j:LocationStateCode.USPostalService>
  <j:LocationPostalCodeID><j:ID>94949</j:ID></j:LocationPostalCodeID>
  <j:LocationStreet>
   <j:StreetNumberText>123</j:StreetNumberText>
   <j:StreetPredirectionalText></j:StreetPredirectionalText>
   <j:StreetName>San Luis Way</j:StreetName>
   <j:StreetTypeText>Boulevard</j:StreetTypeText>
  </j:LocationStreet>
</j:LocationAddress>
</j:Residence>
Here is the query:
SELECT
    201 as Tag,
    NULL as Parent,
    RTRIM(a.AddressId) as [j:Residence!201!j:sourceIDText],
--    1 as [j:Residence!201!order!hide],
    'Residence' as [j:Residence!201!j:LocationTypeText!element],
    20 as [j:Residence!201!j:LocationTypeCode!element],
    NULL as [j:Residence!201!j:LocationAddress!element],
    NULL as [j:LocationAddress!202!j:LocationStreet!element],
    NULL as [j:LocationStreet!203!j:StreetNumberText!element],
    NULL as [j:LocationStreet!203!j:StreetPredirectionalText!element],
    NULL as [j:LocationStreet!203!j:StreetName!element],
    NULL as [j:LocationStreet!203!j:StreetTypeText!element],
    NULL as [j:LocationStreet!203!j:StreetPostdirectionalText!element],
    NULL as [j:LocationStreet!203!j:StreetExtensionText!element],
    NULL as [j:LocationAddress!202!j:LocationSecondaryUnitText!element],
    NULL as [j:LocationAddress!202!j:LocationCityName!element],
    NULL as [j:LocationAddress!202!j:LocationStateCode.USPostalService!element],
    NULL as [j:LocationAddress!202!j:LocationPostalCodeID!xml],
    NULL as [j:LocationAddress!202!j:LocationPostalCodeExtensionID!xml],
    NULL as [j:LocationAddress!202!j:LocationCountryCode.iso3166Alpha!element]
FROM Booking b
INNER JOIN Address a ON
    a.BookId = b.BookId
WHERE b.RespOrgCd = 'SO' AND
    b.BookingNumber BETWEEN '0056100' AND '0056100'
UNION ALL
SELECT
    202 as Tag,
    201 as Parent,
    RTRIM(a.AddressId) as [j:Residence!201!j:sourceIDText],
--    4 as [j:Residence!201!order!hide],
    NULL as [j:Residence!201!j:LocationTypeText!element],
    NULL as [j:Residence!201!j:LocationTypeCode!element],
    NULL as [j:Residence!201!j:LocationAddress!element],
    NULL as [j:LocationAddress!202!j:LocationStreet!element],
    NULL as [j:LocationStreet!203!j:StreetNumberText!element],
    NULL as [j:LocationStreet!203!j:StreetPredirectionalText!element],
    NULL as [j:LocationStreet!203!j:StreetName!element],
    NULL as [j:LocationStreet!203!j:StreetTypeText!element],
    NULL as [j:LocationStreet!203!j:StreetPostdirectionalText!element],
    NULL as [j:LocationStreet!203!j:StreetExtensionText!element],
    RTRIM(a.HmAdUnitId) as [j:LocationAddress!202!j:LocationSecondaryUnitTex!element],
    RTRIM(a.HmCity) as [j:LocationAddress!202!j:LocationCityName!element],
    RTRIM(a.HmState) as [j:LocationAddress!202!j:LocationStateCode.USPostalService!element],
    '<j:ID>' + RTRIM(a.HmZipCode) + '</j:ID>' as [j:LocationAddress!202!j:LocationPostalCodeID!xml],
    '<j:ID>' + NULL + '</j:ID>' as [j:LocationAddress!202!j:LocationPostalCodeExtensionID!xml],
    NULL as [j:LocationAddress!202!j:LocationCountryCode.iso3166Alpha!element]
FROM Booking b
INNER JOIN Address a ON
    a.BookId = b.BookId
WHERE b.RespOrgCd = 'SO' AND
    b.BookingNumber BETWEEN '0056100' AND '0056100'
UNION ALL
SELECT
    203 as Tag,
    202 as Parent,
    RTRIM(a.AddressId) as [j:Residence!201!j:sourceIDText],
--    3 as [j:Residence!201!order!hide],
    NULL as [j:Residence!201!j:LocationTypeText!element],
    NULL as [j:Residence!201!j:LocationTypeCode!element],
    NULL as [j:Residence!201!j:LocationAddress!element],
    NULL as [j:LocationAddress!202!j:LocationStreet!element],
    RTRIM(a.HmStreetNumber) + RTRIM(' ' + a.HmStreetNbrFraction)
        as [j:LocationStreet!203!j:StreetNumberText!element],
    RTRIM(a.HmStreetDir) as [j:LocationStreet!203!j:StreetPredirectionalText!element],
    RTRIM(a.HmStreetName) as [j:LocationStreet!203!j:StreetName!element],
    RTRIM(a.HmStreetTypeCd) as [j:LocationStreet!203!j:StreetTypeText!element],
    NULL as [j:LocationStreet!203!j:StreetPostdirectionalText!element],
    NULL as [j:LocationStreet!203!j:StreetExtensionText!element],
    NULL as [j:LocationAddress!202!j:LocationSecondaryUnitTex!element],
    NULL as [j:LocationAddress!202!j:LocationCityName!element],
    NULL as [j:LocationAddress!202!j:LocationStateCode.USPostalService!element],
    NULL as [j:LocationAddress!202!j:LocationPostalCodeID!xml],
    NULL as [j:LocationAddress!202!j:LocationPostalCodeExtensionID!xml],
    NULL as [j:LocationAddress!202!j:LocationCountryCode.iso3166Alpha!element]
FROM Booking b
INNER JOIN Address a ON
    a.BookId = b.BookId
WHERE b.RespOrgCd = 'SO' AND
    b.BookingNumber BETWEEN '0056100' AND '0056100'
--ORDER BY [j:Residence!201!j:sourceIDText], [j:Residence!201!order!hide]
FOR XML EXPLICIT
I tried putting the last query second. This caused tag not open errors.

Randy
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
January 3, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

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

rstone SSCertifiable Points: 6213 More actions · Answer 1

I sure would appreciate any help with this, even if it is to know that it can't be done with FOR XML EXPLICIT.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 2

Hi, haven't looked at xml explicit in a bit [using oracle at the mo ], but as I recall you should be able to determine the order of things as you want - i think sql server just parses the results from top to bottom. Just one question though - unless I've misread, it looks like you just want to reorder the position of the node under the same parent, which I wouldn't expect to be that important in an xml doc - is this correct?

jon

Not trying to be unhelpful btw, just need to wait for brain to warm up and look at the sql properly

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 3

Don't think you can do what you want simply because in the result set the row for 203 will always occur after 202, so j:LocationStreet ends up being written after the other elements. If you split the info into 2 tags you'll end up with 2 elements and I can't remember any way of merging elements.

The only way I can think of getting round this would be to code the 6 elements you want to appear after this as seperate unions, which I guess would be more than a bit horrible even if you use a table variable to hold the subset of results you're working with. Either that or build the xml tag in text with the !xml directive, but then you'd have encoding probs.

Apologies, not very helpful I know.

rstone SSCertifiable Points: 6213 More actions · Answer 4

Groan. Thanks for the replies. I was hoping for a simple life.

I'm writing the XML to validate against the GJXDM (Global Justice XML Document Model). It determines the sequence.

In the record order, I must have the 202 tag record (open) prior to the 203 tag record. It appears to pull all the non-null 202 tag items at this time. If I specify a non-null value for the "LocationStreet" element, it will show up first, as it should; however, another "LocationStreet" element is created, as before, at the end of the sequence with the 203 tag items.

So, I either get the correct tags (no repeats) in the wrong order (using 3 selects) or the correct order with repeated tags (using 4 selects and adding a "204" tag for the prior 202 items to come last).

How would I add the 6 elements as 6 separate unions? Also, is it possible to get the XML for the node as a sub query or derived table? This might be a way to get the xml using the xml directive without encoding problems. It might make constructing my document easier - the residence is just a part, unfortunately.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 5

Argh! Stupid browser - just lost my post >-| (or maybe these ff mouse gestures aren't compatible with an itchy trigger finger).

If you take off the "for xml explicit" bit and run the query - I believe the parser creates the xml doc by reading left to right, top to bottom, with each row indicating a new tag, which is what is causing the prob.

Is it possible to insert an xsl later in the pipeline btw? It would be a lot easier to use that to create the correct format doc I suspect.

Don't think you can chain "for xml" statements since you can't insert into a temp table. If there is a way it must involve some trickery.

Adding the elements as seperate unions is just more of what you have already, so..

(1) change

NULL as [j:LocationAddress!202!j:LocationSecondaryUnitText!element],

to

NULL as [j:LocationSecondaryUnitText!204!element],

(2) change the LocationSecondaryUnitText {RTRIM(a.HmAdUnitId) ?} in the 202 select to a NULL

(3) add a new union:

UNION ALL

SELECT

204,

202,

NULL,

RTRIM(a.HmAdUnitId),

NULL,

NULL

FROM Booking b

INNER JOIN Address a ON

a.BookId = b.BookId

WHERE b.RespOrgCd = 'SO' AND

b.BookingNumber BETWEEN '0056100' AND '0056100'

(4) ensure the ordering is correct (prob need to include a hidden booking id/ address id in the order by clause) so that the correct sub nodes get added to the correct parent ndoes.

(5) repeat until fed up or have a query several sheets of A4 long

:S

rstone SSCertifiable Points: 6213 More actions · Answer 6

Thanks for the clues. It now works - amazing. A lot of work. I did not work out the "order by" yet. -Randy

Final result:

SET NOCOUNT ON

SELECT RTRIM(a.AddressId) as [sourceIDText],

'Residence' as [LocationTypeText],

'20' as [LocationTypeCode],

RTRIM(a.HmStreetNumber) + RTRIM(' ' + a.HmStreetNbrFraction) as [StreetNumberText],

RTRIM(a.HmStreetDir) as [StreetPredirectionalText],

RTRIM(a.HmStreetName) as [StreetName],

RTRIM(a.HmStreetTypeCd) as [StreetTypeText],

RTRIM(a.HmAdUnitId) as [LocationSecondaryUnitText],

RTRIM(a.HmCity) as [LocationCityName],

RTRIM(a.HmState) as [LocationStateCode.USPostalService],

RTRIM(a.HmZipCode) as [LocationPostalCodeID]

INTO #temp_address

FROM Booking b

INNER JOIN Address a ON

a.BookId = b.BookId

WHERE b.RespOrgCd = 'SO' AND

b.BookingNumber BETWEEN '0056100' AND '0056100'

SELECT Tag = 1, Parent = NULL,

[Residence!1!sourceIDText] = sourceIDText,

[Residence!1!LocationTypeText!element] = LocationTypeText,

[Residence!1!LocationTypeCode!element] = LocationTypeCode,

[LocationAddress!2!] = NULL,

[LocationStreet!3!] = NULL,

[LocationStreet!3!StreetNumberText!element] = NULL,

[LocationStreet!3!StreetPredirectionalText!element] = NULL,

[LocationStreet!3!StreetName!element] = NULL,

[LocationStreet!3!StreetTypeText!element] = NULL,

[LocationSecondaryUnitText!4!] = NULL,

[LocationCityName!5!] = NULL,

[LocationStateCode.USPostalService!6!] = NULL,

[LocationPostalCodeID!7!] = NULL,

[LocationPostalCodeID!7!ID!element] = NULL

FROM #temp_address a

UNION ALL

SELECT Tag = 2, Parent = 1,

[Residence!1!sourceIDText] = sourceIDText,

[Residence!1!LocationTypeText!element] = NULL,

[Residence!1!LocationTypeCode!element] = NULL,

[LocationAddress!2!] = NULL,

[LocationStreet!3!] = NULL,

[LocationStreet!3!StreetNumberText!element] = NULL,

[LocationStreet!3!StreetPredirectionalText!element] = NULL,

[LocationStreet!3!StreetName!element] = NULL,

[LocationStreet!3!StreetTypeText!element] = NULL,

[LocationSecondaryUnitText!4!] = NULL,

[LocationCityName!5!] = NULL,

[LocationStateCode.USPostalService!6!] = NULL,

[LocationPostalCodeID!7!] = NULL,

[LocationPostalCodeID!7!ID!element] = NULL

FROM #temp_address a

UNION ALL

SELECT Tag = 3, Parent = 2,

[Residence!1!sourceIDText] = sourceIDText,

[Residence!1!LocationTypeText!element] = NULL,

[Residence!1!LocationTypeCode!element] = NULL,

[LocationAddress!2!] = NULL,

[LocationStreet!3!] = NULL,

[LocationStreet!3!StreetNumberText!element] = StreetNumberText,

[LocationStreet!3!StreetPredirectionalText!element] = StreetPredirectionalText,

[LocationStreet!3!StreetName!element] = StreetName,

[LocationStreet!3!StreetTypeText!element] = StreetTypeText,

[LocationSecondaryUnitText!4!] = NULL,

[LocationCityName!5!] = NULL,

[LocationStateCode.USPostalService!6!] = NULL,

[LocationPostalCodeID!7!] = NULL,

[LocationPostalCodeID!7!ID!element] = NULL

FROM #temp_address a

UNION ALL

SELECT Tag = 4, Parent = 2,

[Residence!1!sourceIDText] = sourceIDText,

[Residence!1!LocationTypeText!element] = NULL,

[Residence!1!LocationTypeCode!element] = NULL,

[LocationAddress!2!] = NULL,

[LocationStreet!3!] = NULL,

[LocationStreet!3!StreetNumberText!element] = NULL,

[LocationStreet!3!StreetPredirectionalText!element] = NULL,

[LocationStreet!3!StreetName!element] = NULL,

[LocationStreet!3!StreetTypeText!element] = NULL,

[LocationSecondaryUnitText!4!] = LocationSecondaryUnitText,

[LocationCityName!5!] = NULL,

[LocationStateCode.USPostalService!6!] = NULL,

[LocationPostalCodeID!7!] = NULL,

[LocationPostalCodeID!7!ID!element] = NULL

FROM #temp_address a

UNION ALL

SELECT Tag = 5, Parent = 2,

[Residence!1!sourceIDText] = sourceIDText,

[Residence!1!LocationTypeText!element] = NULL,

[Residence!1!LocationTypeCode!element] = NULL,

[LocationAddress!2!] = NULL,

[LocationStreet!3!] = NULL,

[LocationStreet!3!StreetNumberText!element] = NULL,

[LocationStreet!3!StreetPredirectionalText!element] = NULL,

[LocationStreet!3!StreetName!element] = NULL,

[LocationStreet!3!StreetTypeText!element] = NULL,

[LocationSecondaryUnitText!4!] = NULL,

[LocationCityName!5!] = LocationCityName,

[LocationStateCode.USPostalService!6!] = NULL,

[LocationPostalCodeID!7!] = NULL,

[LocationPostalCodeID!7!ID!element] = NULL

FROM #temp_address a

UNION ALL

SELECT Tag = 6, Parent = 2,

[Residence!1!sourceIDText] = sourceIDText,

[Residence!1!LocationTypeText!element] = NULL,

[Residence!1!LocationTypeCode!element] = NULL,

[LocationAddress!2!] = NULL,

[LocationStreet!3!] = NULL,

[LocationStreet!3!StreetNumberText!element] = NULL,

[LocationStreet!3!StreetPredirectionalText!element] = NULL,

[LocationStreet!3!StreetName!element] = NULL,

[LocationStreet!3!StreetTypeText!element] = NULL,

[LocationSecondaryUnitText!4!] = NULL,

[LocationCityName!5!] = NULL,

[LocationStateCode.USPostalService!6!] = [LocationStateCode.USPostalService],

[LocationPostalCodeID!7!] = NULL,

[LocationPostalCodeID!7!ID!element] = NULL

FROM #temp_address a

UNION ALL

SELECT Tag = 7, Parent = 2,

[Residence!1!sourceIDText] = sourceIDText,

[Residence!1!LocationTypeText!element] = NULL,

[Residence!1!LocationTypeCode!element] = NULL,

[LocationAddress!2!] = NULL,

[LocationStreet!3!] = NULL,

[LocationStreet!3!StreetNumberText!element] = NULL,

[LocationStreet!3!StreetPredirectionalText!element] = NULL,

[LocationStreet!3!StreetName!element] = NULL,

[LocationStreet!3!StreetTypeText!element] = NULL,

[LocationSecondaryUnitText!4!] = NULL,

[LocationCityName!5!] = NULL,

[LocationStateCode.USPostalService!6!] = NULL,

[LocationPostalCodeID!7!] = NULL,

[LocationPostalCodeID!7!ID!element] = LocationPostalCodeID

FROM #temp_address a

ORDER BY

[Residence!1!sourceIDText] ASC,

Tag

FOR XML EXPLICIT

drop table #temp_address

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 7

glad to see it's working

the order by is only to ensure that the child nodes get assigned to the correct parent nodes and in whatever order you want, which you've done for sourceIDtext and Tag - is this not giving the order you need?

Couple of asides (and disregard if you already know) you can use a declared table rather than a temp table, which is possibly a bit quicker since it can (in theory) happen just in memory and doesn't have any rollback logging (think you need to use an insert statement though), and you don't need to repeat the field names in each union. Tended to do some ordering up front as well in temp/declared table where I could since it was more readable there. Only mentioning that because I ended up doing quite a few of these a while back and ended up doing that kind of format for the more complicated queries.

regards

rstone SSCertifiable Points: 6213 More actions · Answer 8

It does appear the order is fine. I did not worry about order because this is just the start. The actual document is much uglier - as is most of the stuff in GJXDM which has a xsd of almost 3 MB.

I will take your advice and use a table data type in the final stored procedure. I will process only one booking at a time, so memory will not be a problem.

Thanks for your help.

Randy

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉