從SQL查詢生成特定格式的XML

我需要從下表生成 XML

    DECLARE @tempXML AS TABLE
    (
        ID INT IDENTITY(1,1),
        Field VARCHAR(10),
        VALUE VARCHAR(20),
        LEVEL INT
    )

    INSERT INTO @tempXML
    VALUES
        ('FirstName','FN',2),
        ('LastName','LN',2),
        ('Address','testaddress',1),
        ('City','testcity',1)

XML格式如下圖

<XmlFormat version="1.0">
  <address>
    <field id="Address">testaddress</field>
    <field id="City">testcity</field>   
    <borrower>
      <field id="FirstName">FN</field>
      <field id="LastName">LN</field>      
    </borrower>
  </address>
</XmlFormat>

我嘗試了以下查詢，但沒有以所需的 xml 格式獲得輸出

SELECT 
    field AS 'field/@id',   
    value AS 'field/value'
    FROM @tempXML
    FOR     
    XML PATH('borrower'), ELEMENTS, ROOT('address')

輸出是

<address>
  <borrower>
    <field id="FirstName">
      <value>FN</value>
    </field>
  </borrower>
  <borrower>
    <field id="LastName">
      <value>LN</value>
    </field>
  </borrower>
  <borrower>
    <field id="Address">
      <value>testaddress</value>
    </field>
  </borrower>
  <borrower>
    <field id="City">
      <value>testcity</value>
    </field>
  </borrower>
</address>

我的主要問題是處理級別值（表級別列中的 1 和 2）并以所需的 XML 格式顯示。如果表中還有級別值為 1 和 2 的任何其他條目也應處理。（例如：（'street','teststreet',1）或（'MidName','MN',2）應該輸入正確XML 部分）。

請幫忙

uj5u.com熱心網友回復：

請嘗試以下解決方案。

它使用 XQuery 及其 FLWOR 運算式。

簡單易行，幾乎可以直觀地制作 XML 流程，無??需猜測。

所需的輸出 XML 由兩個步驟組成：

1. 通過創建原始 XMLFOR XML PATH('r'), TYPE, ROOT('root')
2. 通過 FLWOR 運算式組合微調的最終 XML。

SQL

-- DDL and sample data population, start
DECLARE @tbl AS TABLE
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Field VARCHAR(10),
    VALUE VARCHAR(20),
    LEVEL INT
);
INSERT INTO @tbl VALUES
    ('FirstName','FN',2),
    ('LastName','LN',2),
    ('Address','testaddress',1),
    ('City','testcity',1);
-- DDL and sample data population, end

SELECT (
    SELECT * FROM @tbl
    FOR XML PATH('r'), TYPE, ROOT('root')
).query('<XmlFormat version="1.0">
    <address>
        {
            for $x in /root/r[LEVEL="1"]
            return  <field id="{data($x/Field)}">{data($x/VALUE)}</field>
        }
        <borrower>
        {
            for $x in /root/r[LEVEL="2"]
            return  <field id="{data($x/Field)}">{data($x/VALUE)}</field>
        }
        </borrower>
    </address>
</XmlFormat>');

輸出

<XmlFormat version="1.0">
  <address>
    <field id="Address">testaddress</field>
    <field id="City">testcity</field>
    <borrower>
      <field id="FirstName">FN</field>
      <field id="LastName">LN</field>
    </borrower>
  </address>
</XmlFormat>

uj5u.com熱心網友回復：

一種解決方案是為每個“級別”使用子查詢。此示例假定您的表不包含兩個不同的地址，因為我還沒有看到對您的屬性進行分組的方法。

SELECT 
    (
        SELECT 
            field AS 'field/@id',   
            value AS 'field'
        FROM @tempXML
        WHERE LEVEL = 1
        FOR XML PATH(''), TYPE
    )
    ,(
        SELECT 
            field AS 'field/@id',   
            value AS 'field'
        FROM @tempXML
        WHERE LEVEL = 2
        FOR XML PATH(''), Root('borrower'), TYPE        
    )
    FOR XML PATH(''), Root('address'), TYPE

uj5u.com熱心網友回復：

這是c#代碼

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.IO;

namespace ConsoleApplication23
{

    class Program
    {

        static void Main(string[] args)
        {
            XElement address = new XElement("adress");

            XElement borrower = null;

            borrower = new XElement("field", new object[]
            {
                new XElement("field", new object[] {
                    new XAttribute("id", "FirstName"),
                    new XElement("value", "FN")
                })

            });
            address.Add(new XElement("borrower", borrower));

            borrower = new XElement("field", new object[]
            {
                new XElement("field", new object[] {
                    new XAttribute("id", "LastName"),
                    new XElement("value", "LN")
                })

            });
            address.Add(new XElement("borrower", borrower));

            borrower = new XElement("field", new object[]
            {
                new XElement("field", new object[] {
                    new XAttribute("id", "Address"),
                    new XElement("value", "testaddress")
                })

            });
            address.Add(new XElement("borrower", borrower));

            borrower = new XElement("field", new object[]
            {
                new XElement("field", new object[] {
                    new XAttribute("id", "City"),
                    new XElement("value", "testcity")
                })

            });
            address.Add(new XElement("borrower", borrower));



        }
    }
 
}

標籤：sql sql服务器 xml 存储过程 sqlxml

上一篇：使用R進行網路抓取（我想從網站中提取一些類似資料的表格）

下一篇：無法訪問XML資料結構中的“資料”：<NameAttributes>Data</Name>