SQL Server FOR XML-基本查询 [英] SQL Server FOR XML - Basic query
问题描述
已经给了我一个我想通过SQL脚本生成的XML文档，我还没有做过类似的事情，也没有找到任何可以使我生成最终XML的示例.我需要(如果不确定哪种方法更适合我的需要，我不确定(哪种方法可用-EXPLICIT或PATH甚至可能的话). 我希望具有从SQL生成XML的经验的人能够为我指明正确的方向(或者告诉我我要做什么是不可能的，我需要通过子查询来做到)./p>
这种情况是我要从一张表中返回产品详细信息(我希望不必对我需要的每个值进行子查询).
我希望能够生成的xml看起来像(我无法控制这种格式):
<records>
<record>
<fields>
<field name="id">
<values>
<value>666111</value>
</values>
</field>
<field name="name">
<values>
<value>
<![CDATA[My Product Title]]>
</value>
</values>
</field>
</fields>
</record>
<record>
...
</record>
</records>
我看过的第一种方法是使用FOR XML PATH
SELECT TOP 2
'id' AS "@name",
p.product_id as [value],
p.title
FROM products p
ORDER BY p.product_id DESC
FOR XML PATH ('field'), ROOT ('fields'), ELEMENTS;
这给了我XML:
<fields>
<field name="id">
<value>20624</value>
<title>test154</title>
</field>
<field name="id">
<value>20623</value>
<title>test153</title>
</field>
</fields>
哪一个给了我所需的''，但是我无法指定下一个元素所需的布局.
我还研究了FOR XML EXPLICIT
SELECT TOP 2
1 AS Tag, NULL AS Parent,
p.product_id AS [record!1!product_id!ELEMENT],
NULL AS [values!2!value!ELEMENT]
FROM products p
UNION ALL
SELECT TOP 2
2, 1,
p.product_id,
p.title
FROM products p
ORDER BY [record!1!product_id!ELEMENT] DESC
FOR XML EXPLICIT;
哪个给了我以下XML:
<record>
<product_id>20624</product_id>
<values>
<value>test154</value>
</values>
</record>
<record>
<product_id>20623</product_id>
<values>
<value>test153</value>
</values>
</record>
我无法建立请求或获得符合要求的东西(我想我在一次查找中尝试做太多事情，这就是我的问题的原因) ).可以得到任何帮助，即使它为我提供了很好的指导(我发现的唯一示例方面的帮助也很差，它们没有显示出如何构建/更改它们的微妙之处)
这是您可能正在寻找的查询
中间的,''是一个技巧，它使您可以创建多个名称相同的元素，一个在另一个元素的下面……
DECLARE @tbl TABLE(id INT,name VARCHAR(100));
INSERT INTO @tbl VALUES
(666111,'My Product Title 111')
,(666222,'My Product Title 222');
SELECT
(
SELECT 'id' AS [field/@name]
,id AS [field/values/value]
,''
,'name' AS [field/@name]
,name AS [field/values/value]
FOR XML PATH('fields'),TYPE
)
FROM @tbl AS tbl
FOR XML PATH('record'),ROOT('records')
结果
<records>
<record>
<fields>
<field name="id">
<values>
<value>666111</value>
</values>
</field>
<field name="name">
<values>
<value>My Product Title 111</value>
</values>
</field>
</fields>
</record>
<record>
<fields>
<field name="id">
<values>
<value>666222</value>
</values>
</field>
<field name="name">
<values>
<value>My Product Title 222</value>
</values>
</field>
</fields>
</record>
</records>
更新:据我所知，没有干净的方法来添加CDATA -sections
由于某些原因，Microsoft的人们认为CDATA部分不是必需的.好吧，虽然不是，但是有时候还是有要求的...
添加CDATA部分的唯一干净方式是使用FOR XML EXPLICIT.另一个解决方法是放置'|' + name + '#'之类的东西(在您的实际数据中永远不会出现两个字符.
然后，您可以将结果强制转换为NVARCHAR(MAX)，并在字符串基础上替换这些字符.
这将以字符串形式返回您的XML
SELECT
REPLACE(REPLACE(CAST(
(
SELECT
(
SELECT 'id' AS [field/@name]
,id AS [field/values/value]
,''
,'name' AS [field/@name]
,'|' + name + '#' AS [field/values/value]
FOR XML PATH('fields'),TYPE
)
FROM @tbl AS tbl
FOR XML PATH('record'),ROOT('records')
) AS NVARCHAR(MAX)),'|','<![CDATA['),'#',']]>')
此刻，您将其投射回XML时，CDATA消失了:-(
I have been given an XML document that I want to generate via a SQL script, I've not done something like this and haven't been able to find any examples that can lead me to being able to generate the final XML I need (and I'm not sure which of the possible methods available if one is better suited to what I need - EXPLICIT or PATH or if its even possible). I'm hoping somebody with some experience in generating XML from SQL will be able to point me in the right direction (or tell me what I'm trying to do is impossible and that I need to do it with sub-queries).
The scenario is I'm returning product details from a single table (I would prefer to not have to do sub-queries for each of the values I need).
The xml I'm hoping to be able to generate looks like (I have no control over this format):
<records>
<record>
<fields>
<field name="id">
<values>
<value>666111</value>
</values>
</field>
<field name="name">
<values>
<value>
<![CDATA[My Product Title]]>
</value>
</values>
</field>
</fields>
</record>
<record>
...
</record>
</records>
The first method I've looked at is using FOR XML PATH
SELECT TOP 2
'id' AS "@name",
p.product_id as [value],
p.title
FROM products p
ORDER BY p.product_id DESC
FOR XML PATH ('field'), ROOT ('fields'), ELEMENTS;
and this gives me the XML:
<fields>
<field name="id">
<value>20624</value>
<title>test154</title>
</field>
<field name="id">
<value>20623</value>
<title>test153</title>
</field>
</fields>
Which gives me the '' that I need, but I can't then specify the layout I need for the next elements.
I also looked into FOR XML EXPLICIT
SELECT TOP 2
1 AS Tag, NULL AS Parent,
p.product_id AS [record!1!product_id!ELEMENT],
NULL AS [values!2!value!ELEMENT]
FROM products p
UNION ALL
SELECT TOP 2
2, 1,
p.product_id,
p.title
FROM products p
ORDER BY [record!1!product_id!ELEMENT] DESC
FOR XML EXPLICIT;
Which gave me the following XML:
<record>
<product_id>20624</product_id>
<values>
<value>test154</value>
</values>
</record>
<record>
<product_id>20623</product_id>
<values>
<value>test153</value>
</values>
</record>
I'm a bit lost in being able to build up the request or get something that is along the right lines (and I think I'm trying to do too much in a single lookup and that is the cause of my problem). Any help is appreciated - even if its pointing me at a good guide (the only ones I've found have been very poor when it comes to examples - they don't show the subtleties of how you can build/change them)
This is the query you might be looking for
The ,'' in the middle is a trick which allows you to create several elements with the same name one below the other...
DECLARE @tbl TABLE(id INT,name VARCHAR(100));
INSERT INTO @tbl VALUES
(666111,'My Product Title 111')
,(666222,'My Product Title 222');
SELECT
(
SELECT 'id' AS [field/@name]
,id AS [field/values/value]
,''
,'name' AS [field/@name]
,name AS [field/values/value]
FOR XML PATH('fields'),TYPE
)
FROM @tbl AS tbl
FOR XML PATH('record'),ROOT('records')
The result
<records>
<record>
<fields>
<field name="id">
<values>
<value>666111</value>
</values>
</field>
<field name="name">
<values>
<value>My Product Title 111</value>
</values>
</field>
</fields>
</record>
<record>
<fields>
<field name="id">
<values>
<value>666222</value>
</values>
</field>
<field name="name">
<values>
<value>My Product Title 222</value>
</values>
</field>
</fields>
</record>
</records>
UPDATE: As far as I know there is no clean way to add CDATA-sections
For some reasons people at Microsoft think, that CDATA sections are not necessary. Well, they aren't but still sometimes they are demanded...
The only clean way to add CDATA sections was to use FOR XML EXPLICIT. Another workaround was to put something like '|' + name + '#' (use two characters wich will never occur in your actual data.
Then you can cast the result to NVARCHAR(MAX), replace these characters on string base.
This would return your XML as string
SELECT
REPLACE(REPLACE(CAST(
(
SELECT
(
SELECT 'id' AS [field/@name]
,id AS [field/values/value]
,''
,'name' AS [field/@name]
,'|' + name + '#' AS [field/values/value]
FOR XML PATH('fields'),TYPE
)
FROM @tbl AS tbl
FOR XML PATH('record'),ROOT('records')
) AS NVARCHAR(MAX)),'|','<![CDATA['),'#',']]>')
At the moment you cast this back to XML the CDATA is gone :-(
这篇关于SQL Server FOR XML-基本查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
