时间戳

首页 > 解决方案 > SQL XML - 从 SQL Server 为发票创建一个 XML 文件，包括一个 XML 文件中的发票位置

sql-server - SQL XML - 从 SQL Server 为发票创建一个 XML 文件，包括一个 XML 文件中的发票位置

问题描述

我以这种方式创建我的 xml 文件（我不显示所有输出字段，因为有很多字段）：

DECLARE @ID_Rechnung int = 8;

WITH XMLNAMESPACES  (
                        'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' as ext,
                        'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as cbc,
                        'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as cac,
                        'http://uri.etsi.org/01903/v1.3.2#' as xades,
                        'http://www.w3.org/2001/XMLSchema-instance' as xsi,
                        'http://www.w3.org/2000/09/xmldsig#' as ds
                    )    
SELECT
    @XMLData = xmldat.xmldataCol 
FROM
(
    SELECT
        (

        SELECT
            -- HIER XML Daten generieren
            ''                                          AS 'ext:UBLExtensions',
            ''                                          AS 'ext:UBLExtensions/ext:UBLExtension',
            ''                                          AS 'ext:UBLExtensions/ext:UBLExtension/ext:ExtensionContent',
            '2.1'                                       AS 'cbc:UBLVersionID',
            'TR1.2'                                     AS 'cbc:CustomizationID',
            ''                                          AS 'cbc:ProfileID',
            Rechnungen.Nummer                           AS 'cbc:ID',
            'false'                                     AS 'cbc:CopyIndicator',
            ''                                          AS 'cbc:UUID',
            CAST(Rechnungen.Datum AS Date)              AS 'cbc:IssueDate'      
            
        FROM 
            rechnungen  
        WHERE 
            rechnungen.id = @ID_Rechnung


        FOR XML PATH(''), ROOT('Invoice') 
    ) AS xmldataCol

这工作正常 - 我得到以下 XML：

<Invoice xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2">
  <ext:UBLExtensions>
    <ext:UBLExtension>
      <ext:ExtensionContent />
    </ext:UBLExtension>
  </ext:UBLExtensions>
  <cbc:UBLVersionID>2.1</cbc:UBLVersionID>
  <cbc:CustomizationID>TR1.2</cbc:CustomizationID>
  <cbc:ProfileID />
  <cbc:ID>R200001</cbc:ID>
  <cbc:CopyIndicator>false</cbc:CopyIndicator>
  <cbc:UUID />
  <cbc:IssueDate>2020-06-29</cbc:IssueDate>
</Invoice>

但现在我需要在同一个文件中的发票位置。

此 SQL 应包含在第一个 SQL 中，日期应为 xml 文件中的发票行：


SELECT
  Rechnungpos.ID                        AS 'cac:InvoiceLine/cbc:ID',
  Rechnungpos.Anzahl                    AS 'cac:InvoiceLine/cbc:InvoicedQuantity'
FROM
  RechnungPos 
WHERE
  RechnungPos.id_Rechnung = @ID_Rechnung

输出应该是这样的：

<Invoice xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2">
  <ext:UBLExtensions>
    <ext:UBLExtension>
      <ext:ExtensionContent />
    </ext:UBLExtension>
  </ext:UBLExtensions>
  <cbc:UBLVersionID>2.1</cbc:UBLVersionID>
  <cbc:CustomizationID>TR1.2</cbc:CustomizationID>
  <cbc:ProfileID />
  <cbc:ID>R200001</cbc:ID>
  <cbc:CopyIndicator>false</cbc:CopyIndicator>
  <cbc:UUID />
  <cbc:IssueDate>2020-06-29</cbc:IssueDate>
  <cac:InvoiceLine>
    <cbc:ID>1<(cbc:>
    <cbc:InvoicedQuantity>3</cbc:InvoicedQuantity>
  </cac:InvoiceLine>
  <cac:InvoiceLine>
    <cbc:ID>5<(cbc:>
    <cbc:InvoicedQuantity>1</cbc:InvoicedQuantity>
  </cac:InvoiceLine>
  <cac:InvoiceLine>
    <cbc:ID>9<(cbc:>
    <cbc:InvoicedQuantity>2</cbc:InvoicedQuantity>
  </cac:InvoiceLine>
</Invoice>

这是生成测试数据的代码：

CREATE TABLE [dbo].[Rechnungen](
    [id] [int]  NOT NULL,
    [Nummer] [nvarchar](20) NOT NULL,
    [Datum] [datetime] NOT NULL
)

INSERT INTO Rechnungen (id, Nummer, Datum) VALUES (8, 'R200001', '29.06.2020')

CREATE TABLE [dbo].Rechnungpos(
    [id] [int]  NOT NULL,
    [id_Rechnung] [int] NOT NULL,
    [Anzahl] [float] NOT NULL
)

INSERT INTO RechnungPos (id, id_Rechnung, Anzahl) VALUES (1, 8, 3) 
INSERT INTO RechnungPos (id, id_Rechnung, Anzahl) VALUES (5, 8, 1) 
INSERT INTO RechnungPos (id, id_Rechnung, Anzahl) VALUES (9, 8, 2)

它必须在不同版本上运行 - 我的版本是 SQL Server 2019

我怎样才能做到这一点？

谢谢你的帮助，托马斯。

标签： sql-serverxmltsqlxquery

解决方案

这是如何做到的。

唯一的复杂性是如何处理输出 XML 中的子表和特别是名称空间。这就是使用 XQuery 及其 FLWOR 表达式的原因。

SQL

-- DDL and sample data population, start
DECLARE @Rechnungen TABLE (id int , Nummer nvarchar(20), Datum datetime);
INSERT INTO @Rechnungen (id, Nummer, Datum) VALUES 
(8, 'R200001', '2020-06-29');

DECLARE @Rechnungpos TABLE (id int, id_Rechnung int, Anzahl float);
INSERT INTO @RechnungPos (id, id_Rechnung, Anzahl) VALUES 
(1, 8, 3),
(5, 8, 1), 
(9, 8, 2);
-- DDL and sample data population, end

DECLARE @ID_Rechnung int = 8;

;WITH XMLNAMESPACES ('urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' as ext
    , 'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as cbc
    , 'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as cac
    , 'http://uri.etsi.org/01903/v1.3.2#' as xades
    , 'http://www.w3.org/2001/XMLSchema-instance' as xsi
    , 'http://www.w3.org/2000/09/xmldsig#' as ds)
SELECT (
SELECT '2.1'               AS [cbc:UBLVersionID],
    'TR1.2'                AS [cbc:CustomizationID],
    ''                     AS [cbc:ProfileID],
    p.Nummer               AS [cbc:ID],
    'false'                AS [cbc:CopyIndicator],
    ''                     AS [cbc:UUID],
    CAST(p.Datum AS Date)  AS [cbc:IssueDate],
    (
        SELECT c.id AS [cbc:ID]
            , CAST(c.Anzahl AS INT) AS [cbc:InvoicedQuantity] 
        FROM @Rechnungpos AS c INNER JOIN 
            @Rechnungen AS p ON p.id = c.id_Rechnung
        FOR XML PATH('r'), TYPE, ROOT('root')
    )
FROM @Rechnungen AS p
WHERE p.id = @ID_Rechnung
FOR XML PATH(''), TYPE, ROOT('Invoice')
).query('<Invoice xmlns:ds="http://www.w3.org/2000/09/xmldsig#"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xmlns:xades="http://uri.etsi.org/01903/v1.3.2#"
         xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"
         xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"
         xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2">
<ext:UBLExtensions>
    <ext:UBLExtension>
        <ext:ExtensionContent/>
    </ext:UBLExtension>
</ext:UBLExtensions>
{
   for $x in /Invoice/*[local-name()!="root"]
   return $x,
     for $x in /Invoice/root/r
     return <cac:InvoiceLine>{$x/*}</cac:InvoiceLine>
}
</Invoice>');

输出

<Invoice xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2">
  <ext:UBLExtensions>
    <ext:UBLExtension>
      <ext:ExtensionContent />
    </ext:UBLExtension>
  </ext:UBLExtensions>
  <cbc:UBLVersionID>2.1</cbc:UBLVersionID>
  <cbc:CustomizationID>TR1.2</cbc:CustomizationID>
  <cbc:ProfileID />
  <cbc:ID>R200001</cbc:ID>
  <cbc:CopyIndicator>false</cbc:CopyIndicator>
  <cbc:UUID />
  <cbc:IssueDate>2020-06-29</cbc:IssueDate>
  <cac:InvoiceLine>
    <cbc:ID>1</cbc:ID>
    <cbc:InvoicedQuantity>3</cbc:InvoicedQuantity>
  </cac:InvoiceLine>
  <cac:InvoiceLine>
    <cbc:ID>5</cbc:ID>
    <cbc:InvoicedQuantity>1</cbc:InvoicedQuantity>
  </cac:InvoiceLine>
  <cac:InvoiceLine>
    <cbc:ID>9</cbc:ID>
    <cbc:InvoicedQuantity>2</cbc:InvoicedQuantity>
  </cac:InvoiceLine>
</Invoice>

推荐阅读