XML qry: incorporating a tag into another

• petitpere

  SSCrazy

  Points: 2520

  More actions

  May 16, 2014 at 8:44 am

  #286770

  Hi all,

  I'm coming back to you with a new issue with XML queries.

  I have one that gives following output (cut):

  [...]

  <Amt>

  <InstdAmt>15303.10</InstdAmt>

  <Ccy>EUR</Ccy>

  </Amt>

  [...]

  but I'd like to have it look like:

  [...]

  <Amt>

  <InstdAmt Ccy="EUR">15303.10</InstdAmt>

  </Amt>

  [...]

  The query is more than 3000 lines long, so I'm not gonna post it here right now but this is the code I used for these 2 elements to give you an idea:

  [...]

  select 37 as Tag,

  35 as Parent,

  PmtInf_PmtInfId as [Document!1!Sort!Hide],

  null as [Document!1!],

  [...]

  null as [Amt!35!],

  PmtInf_CdtTrfTxInf_InstdAmt as [InstdAmt!36!],

  PmtInf_CdtTrfTxInf_InstdCcy as [Ccy!37!],

  [...]

  I'm searching but can't figure out how to achieve this.

  Any help will be much appreciated. 🙂

  Thanks a lot and let me know if you need more informations.

  rot

• Eirikur Eiriksson

  SSC Guru

  Points: 182881

  More actions

  May 16, 2014 at 10:54 am

  #1714266

  Prefixing the output column alias with the @ (ascii 64) sign does the trick

  😎

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  May 16, 2014 at 2:21 pm

  #1714341

  Also - you might care to switch over to using FOR XML PATH rather than EXPLICIT. You will get the same level of control over the content and layout, but without all of those crazy extra level markers, etc....

  Clearly - this would have to be managed, since you're rewriting the query, but the maintenance is a LOT easier once you've done it.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• petitpere

  SSCrazy

  Points: 2520

  More actions

  May 19, 2014 at 6:31 am

  #1714591

  Thanks a lot Eirikur and Matt for your answers ...

  The @ sign did the trick and I was looking so long for this ... :crazy:

  I was wondering how I could ever maintain a 3000 lines long query and did not know I could use FOR XML PATH instead of EXPLICIT. I rewrote the query and the number of lines was cut by a factor of ten! Amazing and so fluid to read. :satisfied:

  Thanks very, very much you both for your precious help.