从SQL Server中的两个表生成XML

Can somebody help me with producing XML from two sql tables?

有人可以帮助我从两个sql表生成XML吗？

This is what I want:

这就是我要的：

<Sales>
  <Sale>
    <Journal_Prime>400000</Journal_Prime>
    <DocNumber>100001</DocNumber>
    <Details>
        <Detail>
          <Account>700300</Account>
          <Amount>276,79</Amount>
          <DebCre>-1</DebCre>
          <Ventil>70</Ventil>
          <Ref>WD2093E0V0</Ref>
          <DocNumber>100001</DocNumber>
        </Detail>
        <Detail>
          <Account>708000</Account>
          <Amount>0,00</Amount>
          <DebCre>1</DebCre>
          <Ventil>70</Ventil>
          <Ref>Korting</Ref>
          <DocNumber>100001</DocNumber>
        </Detail>
        <Detail>
          <Account>700530</Account>
          <Amount>55,00</Amount>
          <DebCre>-1</DebCre>
          <Ventil>70</Ventil>
          <Ref>Transport</Ref>
          <DocNumber>100001</DocNumber>
        </Detail>
        <Detail>
          <Account>451000</Account>
          <Amount>0,00</Amount>
          <DebCre>-1</DebCre>
          <Ventil>11</Ventil>
          <Ref>BTW</Ref>
          <DocNumber>100001</DocNumber>
        </Detail>
    </Details>
  </Sale>
</Sales>

This is my attempt

这是我的尝试

SELECT Sale.Journal_Prime, Sale.DocNumber, Detail.Account, Detail.Account, Detail.Amount, Detail.DebCre, Detail.Ventil, Detail.Ref, Detail.DocNumber
FROM XML_FAKAdres2017  as Sale 

INNER JOIN XML_FAK2017 as Detail
ON Sale.DocNumber = Detail.DocNumber

FOR XML AUTO, ROOT('Sales'), ELEMENTS

giving me this result

给我这个结果

<Sales>
  <Sale>
    <Journal_Prime>400000</Journal_Prime>
    <DocNumber>100001</DocNumber>
        <Detail>
          <Account>700300</Account>
          <Amount>276,79</Amount>
          <DebCre>-1</DebCre>
          <Ventil>70</Ventil>
          <Ref>WD2093E0V0</Ref>
          <DocNumber>100001</DocNumber>
        </Detail>
        <Detail>
          <Account>708000</Account>
          <Amount>0,00</Amount>
          <DebCre>1</DebCre>
          <Ventil>70</Ventil>
          <Ref>Korting</Ref>
          <DocNumber>100001</DocNumber>
        </Detail>
        <Detail>
          <Account>700530</Account>
          <Amount>55,00</Amount>
          <DebCre>-1</DebCre>
          <Ventil>70</Ventil>
          <Ref>Transport</Ref>
          <DocNumber>100001</DocNumber>
        </Detail>
        <Detail>
          <Account>451000</Account>
          <Amount>0,00</Amount>
          <DebCre>-1</DebCre>
          <Ventil>11</Ventil>
          <Ref>BTW</Ref>
          <DocNumber>100001</DocNumber>
        </Detail>
  </Sale>
</Sales>

So, I'm missing the <Details></Details> which is required by the bookkeeping program this code is meant for import. I'm not familiar with XML and to be honest I don't have a clue where this coming from.

所以，我错过了簿记程序所需的

这个代码用于导入。我不熟悉XML，说实话，我不知道这是从哪里来的。

Thanks. Rik

谢谢。里克

1 个解决方案

#1

Try this

尝试这个

SELECT Sale.Journal_Prime, Sale.DocNumber , 
(SELECT Detail.Account, Detail.Account, Detail.Amount, 
Detail.DebCre, Detail.Ventil, Detail.Ref, Detail.DocNumber
FROM XML_FAK2017 as Detail where Sale.DocNumber = Detail.DocNumber
FOR XML AUTO,TYPE,ROOT('Details'),ELEMENTS)
FROM XML_FAKAdres2017  as Sale 
FOR XML AUTO, ROOT('Sales'),ELEMENTS