SQL XML 列的元素来分隔列

coder 2024-06-30 原文

我有一个带有 XML 列的 SQL 表。我想将 XML 元素分离到 View 中它们自己的列中。

我可以使用 .value 来做到这一点，但我并不总是知道元素名称是什么。例如，在下面的选择中，缺少 c 元素。

create table #temp (Id int, Name varchar(32), taskdata xml)

insert into #temp values
(1, 'Fred','<data><a>Red</a><b>Apple</b></data>'),
(2, 'Mary','<data><a>Blue</a><b>Ball</b></data>'),
(3, 'Paul','<data><a>Green</a><b>Tree</b></data>'),
(4, 'Lisa','<data><a>Yellow</a><b>Hat</b><c>House</c></data>')

select Id
      ,Name
      ,Taskdata.value('(/data/a)[1]', 'nvarchar(max)') AS a
      ,Taskdata.value('(/data/b)[1]', 'nvarchar(max)') AS b
from #temp

drop table #temp

我可以使用以下方法获取所有元素名称的列表:

select distinct T.N.value('local-name(.)','nvarchar(64)') ColNames
from #temp
cross apply Taskdata.nodes('//data/*') as T(N)

但是我不知道如何替换:

Taskdata.value('(/data/a)[1]', 'nvarchar(max)') AS a

为了更动态的东西。

最佳答案

编辑

如果您需要一种完全通用的方法，您可以尝试动态 SQL:

DECLARE @cmd VARCHAR(1000)=
'select Id
      ,Name' + 
      (
      SELECT DISTINCT',Taskdata.value(''(/data/' + TheNode.value('local-name(.)','nvarchar(64)')  + ')[1]'', ''nvarchar(max)'') AS [' + TheNode.value('local-name(.)','nvarchar(64)') + '] '
      FROM #temp AS innerT
      CROSS APPLY innerT.taskdata.nodes('/data/*') AS ThisIs(TheNode)
      FOR XML PATH('')
      )
      +
'from #temp;'
EXEC (@cmd);

EDIT 2 - 用它来创建一个 View

View 无法使用临时表，必须将您的#temp 更改为普通表...

create table temp (Id int, Name varchar(32), taskdata xml)

insert into temp values
(1, 'Fred','<data><a>Red</a><b>Apple</b></data>'),
(2, 'Mary','<data><a>Blue</a><b>Ball</b></data>'),
(3, 'Paul','<data><a>Green</a><b>Tree</b></data>'),
(4, 'Lisa','<data><a>Yellow</a><b>Hat</b><c>House</c></data>')

DECLARE @cmd VARCHAR(1000)=
'CREATE VIEW dbo.SomeName AS select Id
      ,Name' + 
      (
      SELECT DISTINCT',Taskdata.value(''(/data/' + TheNode.value('local-name(.)','nvarchar(64)')  + ')[1]'', ''nvarchar(max)'') AS [' + TheNode.value('local-name(.)','nvarchar(64)') + '] '
      FROM temp AS innerT
      CROSS APPLY innerT.taskdata.nodes('/data/*') AS ThisIs(TheNode)
      FOR XML PATH('')
      )
      +
'from temp;'
EXEC (@cmd);
GO

SELECT * FROM dbo.SomeName;
GO

drop view dbo.SomeName;
drop table temp;

上一个

XML 的问题是:您必须了解结构，至少了解您的数据的一些共同点:是否总有一个根元素“数据”？是否总是有 1:n 个内部元素而没有其他元素？他们的最大数量是多少？如果你有 a 和 c 但没有 b，你怎么知道缺少哪个元素？

这是一种方法:

select Id
      ,Name
      ,Taskdata.value('/data[1]/*[1]', 'nvarchar(max)') AS a
      ,Taskdata.value('/data[1]/*[2]', 'nvarchar(max)') AS b
      ,Taskdata.value('/data[1]/*[3]', 'nvarchar(max)') AS c
from #temp

如果你通过查询知道内部元素的名称，你会得到相同的结果

      ,Taskdata.value('(/data/c)[1]', 'nvarchar(max)') AS c

结果

Id  Name    a       b       c
1   Fred    Red     Apple   NULL
2   Mary    Blue    Ball    NULL
3   Paul    Green   Tree    NULL
4   Lisa    Yellow  Hat     House

关于SQL XML 列的元素来分隔列，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/36197524/

素来 SQL 39 data gt sql-server xml sql-server-2012

有关SQL XML 列的元素来分隔列的更多相关文章

ruby-on-rails - 使用 ruby 将多个实例变量转换为散列的更好方法？ - 2
我收到格式为的回复#我需要将其转换为哈希值(针对活跃商家)。目前我正在遍历变量并执行此操作:response.instance_variables.eachdo|r|my_hash.merge!(r.to_s.delete("@").intern=>response.instance_eval(r.to_s.delete("@")))end这有效，它将生成{:first="charlie",:last=>"kelly"},但它似乎有点hacky和不稳定。有更好的方法吗？编辑:我刚刚意识到我可以使用instance_variable_get作为该等式的第二部分，但这仍然是主要问题。
Ruby:如何使用带有散列的 'send' 方法调用方法？ - 2
假设我有一个类A，里面有一些方法。假设stringmethodName是这些方法之一，我已经知道我想给它什么参数。它们在散列中{'param1'=>value1,'param2'=>value2}所以我有:params={'param1'=>value1,'param2'=>value2}a=A.new()a.send(methodName,value1,value2)#callmethodnamewithbothparams我希望能够通过传递我的哈希以某种方式调用该方法。这可能吗？最佳答案确保methodName是一个符号，而
要散列的 Ruby 数组 - 2
我有以下数组:myarray=[['usa','primary','john'],['france','primary','lira'],['usa','secondary','steve'],['germany','primary','jeff'],['france','secondary','ben']]我想将它转换成一个散列数组，如下所示:[{:country=>'usa',:primary=>'john',:secondary=>'steve'},{:country=>'france',:primary=>'lira',:secondary=>'ben'},{:country=
ruby-on-rails - 向现有表添加非空列的问题 - 2
我在向表中添加不可为空的列时遇到问题。我看了很多关于这个的帖子，它应该是正确的。迁移代码:defchangeadd_column:individual_trainings,:start_on,:timeadd_column:individual_trainings,:end_on,:timechange_column_null:individual_trainings,:start_on,falsechange_column_null:individual_trainings,:end_on,falseend错误:PG::NotNullViolation:ERROR:column"st
ruby-on-rails - 如何以递归方式将 YAML 文件扁平化为 JSON 对象，其中键是点分隔的字符串？ - 2
例如，如果我有YAML文件en:questions:new:'NewQuestion'other:recent:'Recent'old:'Old'这最终会变成一个json对象，例如{'questions.new':'NewQuestion','questions.other.recent':'Recent','questions.other.old':'Old'} 最佳答案由于问题是关于在Rails应用程序上使用YAML文件进行i18n，因此值得注意i18ngem提供了一个辅助模块I18n::Backend::Flatten完全像
ruby-on-rails - 如何添加具有额外列的多对多记录 - 2
我有以下模型用户has_many:users_contactshas_many:contacts,through::users_contactsaccepts_nested_attributes_for:contacts,allow_destroy:true联系方式has_many:users_contactshas_many:users,through::users_contactsaccepts_nested_attributes_for:users_contacts,allow_destroy:true用户联系belongs_to:usersbelongs_to:contacts
ruby-on-rails - 在 Rails 中，将散列压缩为嵌套散列的最佳方法是什么 - 2
假设我有这个:[{:user_id=>1,:search_id=>a},{:user_id=>1,:search_id=>b},{:user_id=>2,:search_id=>c},{:user_id=>2,:search_id=>d}]我想结束:[{:user_id=>1,:search_id=>[a,b]},{:user_id=>2,:search_id=>[c,d]}]最好的方法是什么？最佳答案确实是非常奇怪的要求。无论如何[{:user_id=>1,:search_id=>"a"},{:user_id=>1,:sear
ruby - 如何分隔 DynamoDB 更新表达式中的多个子句 - 2
根据AWSDocs:Anupdateexpressionconsistsofoneormoreclauses.EachclausebeginswithaSET,REMOVE,ADDorDELETEkeyword.Youcanincludeanyoftheseclausesinanupdateexpression,inanyorder.However,eachactionkeywordcanappearonlyonce.我无法在一个update_expression中获得正确的SET和REMOVE语法:params={key:{'id'=>{s:'123'}},table_name:'c
ruby - 如何使用正则表达式用逗号分隔字符串(括号内除外)？ - 2
我想用逗号分割一个字符串:"a,s".split','#=>['a','s']如果子字符串被括号包裹，我不想拆分它:"a,s(d,f),g,h"应该产生:['a','s(d,f)','g','h']有什么建议吗？最佳答案要处理嵌套括号，可以使用:txt="a,s(d,f(4,5)),g,h"pattern=Regexp.new('((?:[^,(]+|($(?>[^()]+|\g)*$))+)')putstxt.scan(pattern).map&:first图案细节:(#firstcapturinggroup(?:#ope
ruby - 为什么百分号中使用不同的分隔符？ - 2
我见过不同的人为此使用不同类型的牙套/括号。我在脚本控制台中试用了它们，它们都有效。为什么它们都有效？使用哪个有关系吗？%w|onetwo|%w{onetwo}%w[onetwo]%w(onetwo)实际上，可以使用更多种类的字符。可以使用除=之外的任何非字母数字字符。%w!a!%w@b@%w#c#%w$d$%w%e%%w^f^%w&g&%w*h*%w(i)%w_j_%w-k-%w+l+%w\m\%w|n|%w`o`%w~p~%w[q]%w{r}%w;s;%w:t:%w'u'%w"v"%w,w,%w%w.y.%w/z/%w?aa? 最佳答案

SQL XML 列的元素来分隔列

编辑

EDIT 2 - 用它来创建一个 View

上一个

有关SQL XML 列的元素来分隔列的更多相关文章

随机推荐