XQuery 和节点 ID [英] XQuery and Node Ids
问题描述
我有这个变量:
声明@xmlDoc XML其中存储了以下 xml:
我有一个用于获取数据的查询:
声明@xmlDoc XMLset @xmlDoc = '' - Stack Overflow 无法在一行上处理所有 xml.选择——参数 1TBL.SParam.value('local-name((*)[1])', 'varchar(50)') 作为 Param1Name,TBL.SParam.value('(*)[1]', 'varchar(100)') 作为 Param1Value,-- 参数 2TBL.SParam.value('local-name((*)[2])', 'varchar(50)') 作为 Param2Name,TBL.SParam.value('(*)[2]', 'varchar(100)') 作为 Param2Value,-- 参数 3TBL.SParam.value('local-name((*)[3])', 'varchar(50)') 作为 Param3Name,TBL.SParam.value('(*)[3]', 'varchar(100)') 作为 Param3Value,-- 参数 4TBL.SParam.value('local-name((*)[4])', 'varchar(50)') 作为 Param4Name,TBL.SParam.value('(*)[4]', 'varchar(100)') 作为 Param4Value,-- 参数 5TBL.SParam.value('local-name((*)[5])', 'varchar(50)') 作为 Param5Name,TBL.SParam.value('(*)[5]', 'varchar(100)') 作为 Param5ValueFROM @xmldoc.nodes('/NewDataSet/Table1') AS TBL(SParam)我需要一种方法将它们来自 xml 文件的顺序添加到我的结果中.(这是 Table1 的第一个实例，然后是第二个......).
由于 SQL 表变量限制的原因，我不能使用身份列来保持这一点.(由于其他原因，我不想使用临时表.)
我希望有一个很酷的 SQL XML 函数可以返回某种内部分配的节点 ID.(或其他类似的排序方式.)
请注意，我不控制此 XML 结构(我只是一名读者)，因此我无法进行更改以添加 ID 属性.
任何建议都会很棒！
编辑/更新:
我真的很想拥有这样的数据:
<前>1 |共享参数 |共享1 |花粉 |萨雷1 |随机参数2 |好东西1 |更多参数 |和更多1 |结果参数 |和更多2 |随机参数2 |你2 |更多参数 |思考2 |结果参数 |23 |共享参数 |最后的3 |花粉 |放...但我来不及了.我可以将它放入列中(或多或少)，但我不知道如何进行编号.如果您有任何想法，我很想听听.
我想出了执行此操作的查询(在互联网的帮助下).它看起来像这样:
SELECT TBL.SParam.value('local-name(.)[1]', 'varchar(50)') 作为参数名，TBL.SParam.value('(.)[1]', 'varchar(50)') ParamValue,TBL.SParam.value('for $s in . return count(../*[. << $s]) + 1', 'int') ParamPosition,TBL.SParam.value('for $s in . return count(../../*[. << $s]) - 1', 'int') ParamIterationFROM @xmldoc.nodes('/NewDataSet/Table1/*') AS TBL(SParam)您可以使用数字表和 位置()
SELECT N.Number 作为 ID，-- 参数 1TBL.SParam.value('local-name((*)[1])', 'varchar(50)') 作为 Param1Name,TBL.SParam.value('(*)[1]', 'varchar(100)') 作为 Param1Value,-- 参数 2TBL.SParam.value('local-name((*)[2])', 'varchar(50)') 作为 Param2Name,TBL.SParam.value('(*)[2]', 'varchar(100)') 作为 Param2Value,-- 参数 3TBL.SParam.value('local-name((*)[3])', 'varchar(50)') 作为 Param3Name,TBL.SParam.value('(*)[3]', 'varchar(100)') 作为 Param3Value,-- 参数 4TBL.SParam.value('local-name((*)[4])', 'varchar(50)') 作为 Param4Name,TBL.SParam.value('(*)[4]', 'varchar(100)') 作为 Param4Value,-- 参数 5TBL.SParam.value('local-name((*)[5])', 'varchar(50)') 作为 Param5Name,TBL.SParam.value('(*)[5]', 'varchar(100)') 作为 Param5ValueFROM master..spt_values 为 N交叉应用@xmldoc.nodes('/NewDataSet/Table1[position()=sql:column("N.Number")]') AS TBL(SParam)其中 N.type = 'P' 和N.number 介于 1 和 @xmlDoc.value('count(/NewDataSet/Table1)', 'int')I have this variable:
declare @xmlDoc XML
it has the following xml stored in it:
<?xml version="1.0" encoding="utf-8"?>
<NewDataSet>
<Table1>
<Sharedparam>shared</Sharedparam>
<Antoher>sahre</Antoher>
<RandomParam2>Good stuff</RandomParam2>
<MoreParam>and more</MoreParam>
<ResultsParam>2</ResultsParam>
</Table1>
<Table1>
<RandomParam2>do you</RandomParam2>
<MoreParam>think</MoreParam>
<ResultsParam>2</ResultsParam>
</Table1>
<Table1>
<Sharedparam>Last</Sharedparam>
<Antoher> Set </Antoher>
<RandomParam2> of </RandomParam2>
<MoreParam>values</MoreParam>
<ResultsParam>are here</ResultsParam>
</Table1>
<Table1 />
</NewDataSet>
I have this query that I am using to get the data:
declare @xmlDoc XML
set @xmlDoc = '' -- Stack Overflow could not handle the xml all on one line.
SELECT -- Param 1
TBL.SParam.value('local-name((*)[1])', 'varchar(50)') as Param1Name,
TBL.SParam.value('(*)[1]', 'varchar(100)') as Param1Value,
-- Param2
TBL.SParam.value('local-name((*)[2])', 'varchar(50)') as Param2Name,
TBL.SParam.value('(*)[2]', 'varchar(100)') as Param2Value,
-- Param3
TBL.SParam.value('local-name((*)[3])', 'varchar(50)') as Param3Name,
TBL.SParam.value('(*)[3]', 'varchar(100)') as Param3Value,
-- Param 4
TBL.SParam.value('local-name((*)[4])', 'varchar(50)') as Param4Name,
TBL.SParam.value('(*)[4]', 'varchar(100)') as Param4Value,
-- Param 5
TBL.SParam.value('local-name((*)[5])', 'varchar(50)') as Param5Name,
TBL.SParam.value('(*)[5]', 'varchar(100)') as Param5Value
FROM @xmldoc.nodes('/NewDataSet/Table1') AS TBL(SParam)
I need a way to add to my results the order that they came from the xml file. (Which was the first instance of Table1, then the second....).
For reasons of SQL table variable limitations, I can't use an identity column to keep this straight. (For other reasons, I don't want to use a temporary table.)
I am hoping that there is a cool SQL XML function that will return some kind of internally assigned Node ID. (Or some other similar manner of ordering.)
Note, I do not control this XML Structure (I am a reader only) so I cannot make changes to add in an ID attribute.
Any advice would be great!
EDIT/Update:
I would really like to have this data like this:
1 | SharedParam | shared 1 | Antoher | sahre 1 | RandomParam2 | Good stuff 1 | MoreParam | and more 1 | ResultsParam | and more 2 | RandomParam2 | do you 2 | MoreParam | think 2 | ResultsParam | 2 3 | Sharedparam | Last 3 | Antoher | Set . . .
But I am coming up short. I can get it into columns (more or less), but I don't know how to do the numbering. If you have any ideas I would love to hear them.
EDIT:
I figured out the query to do this (with some help from the internet). It looks like this:
SELECT TBL.SParam.value('local-name(.)[1]', 'varchar(50)') as ParamName,
TBL.SParam.value('(.)[1]', 'varchar(50)') ParamValue,
TBL.SParam.value('for $s in . return count(../*[. << $s]) + 1', 'int') ParamPosition,
TBL.SParam.value('for $s in . return count(../../*[. << $s]) - 1', 'int') ParamIteration
FROM @xmldoc.nodes('/NewDataSet/Table1/*') AS TBL(SParam)
You can use a number table and position()
SELECT N.Number as ID,
-- Param 1
TBL.SParam.value('local-name((*)[1])', 'varchar(50)') as Param1Name,
TBL.SParam.value('(*)[1]', 'varchar(100)') as Param1Value,
-- Param2
TBL.SParam.value('local-name((*)[2])', 'varchar(50)') as Param2Name,
TBL.SParam.value('(*)[2]', 'varchar(100)') as Param2Value,
-- Param3
TBL.SParam.value('local-name((*)[3])', 'varchar(50)') as Param3Name,
TBL.SParam.value('(*)[3]', 'varchar(100)') as Param3Value,
-- Param 4
TBL.SParam.value('local-name((*)[4])', 'varchar(50)') as Param4Name,
TBL.SParam.value('(*)[4]', 'varchar(100)') as Param4Value,
-- Param 5
TBL.SParam.value('local-name((*)[5])', 'varchar(50)') as Param5Name,
TBL.SParam.value('(*)[5]', 'varchar(100)') as Param5Value
FROM master..spt_values as N
cross apply @xmldoc.nodes('/NewDataSet/Table1[position()=sql:column("N.Number")]') AS TBL(SParam)
where N.type = 'P' and
N.number between 1 and @xmlDoc.value('count(/NewDataSet/Table1)', 'int')
这篇关于XQuery 和节点 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
