从SQL查询生成特定格式的XML

Question

我需要从下面的表

生成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>

我的主要问题是处理Level值(表中的Level列中的1和2)并以所需的XML格式显示。如果表中有任何具有级别值1和2的额外条目，也应该处理。(例如:('street'，'teststreet'，1)或('MidName'，'MN'，2)应该出现在XML的正确部分)。

请帮

Answer 1

请尝试以下解决方案。

它正在使用XQuery及其FLWOR表达式。

简单易用，几乎可以直观地制作XML过程，无需猜测。

所需的XML输出分两步组成:

1. 通过FOR XML PATH('r'), TYPE, ROOT('root')创建原始XML
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>

Answer 2

一个解决方案是为每个"级别"使用子查询。这个例子假设，你的表不包含两个不同的地址，因为我还没有看到一个方法来分组你的属性。

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

Answer 3

下面是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));

}
}

}