jjzjj

sql-server - sql中openxml的动态列和记录

coder 2024-07-04 原文

我有一个非常简单的 xml 文档。唯一的区别是元素可以改变。有一次我可能会:

<data><PersonalInfo>
<Person><FirstName>Bob</FirstName><LastName>Smith</LastName></Person>
<Person><FirstName>John</FirstName><LastName>Doe</LastName></Person>
</PersonalInfo></data>

下次我可能会:

<data><AddressInfo>
<Address><City>Cleveland</City><State>OH</State></Address>
<Address><City>Chicago</City><State>IL</State></Address>
</AddressInfo></data>

我想编写一个 select 语句，根据我目前获得的 xml 文档生成一个动态表。

例如:对于第一个:

First Name     Last Name
------------------------
Bob             Smith
John            Doe
Etc...

第二个

City       State
-----------------------
Cleveland   OH
Chicago     IL
Etc...

这两个例子没有任何关系(Bob 不是来自克利夫兰等...)

我只想使用相同的代码生成两个表...取决于 xml 文档。当然，唯一的区别是节点引用:

Example 1:  data/PersonalInfo/Person*
Example 2:  data/AddressInfo/Address*

我不想合并或更改 xml 文档结构中的任何内容。 它们就是它们要进来的东西。我如何引用每一个来创建上面的两个不同的表 - 每个进来的 xml 文档都将在一个单独的运行存储过程中。但它将是相同的存储过程。非常感谢任何帮助，在此先感谢!

最佳答案

像这样尝试:

CREATE PROCEDURE dbo.TransformPlainXML(@InputXml XML)
AS
BEGIN

DECLARE @PivotColumns NVARCHAR(MAX);

WITH DistinctElementNames AS
(
    SELECT DISTINCT '[' + Element.value('fn:local-name(.)','varchar(max)') + ']' AS ElementName
    FROM @InputXml.nodes('/data/*/*/*') As One(Element)
)
SELECT @PivotColumns = STUFF(
(
    SELECT ',' + ElementName
    FROM DistinctElementNames
    FOR XML PATH('')
),1,1,'');

DECLARE @cmd NVARCHAR(MAX)=
'WITH Lines AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowIndex
          ,Line.query(''.'') AS OneLine
    FROM @xml.nodes(''/data/*/*'') As One(Line)
)
SELECT p.*
FROM
(
    SELECT RowIndex
          ,Element.value(''.[1]'',''varchar(max)'') AS ElementValue
          ,Element.value(''fn:local-name(.)'',''varchar(max)'') AS ElementName
    FROM Lines
    CROSS APPLY OneLine.nodes(''./*/*'') AS The(Element)
) AS tbl
PIVOT
(
    MIN(ElementValue) FOR ElementName IN(' + @PivotColumns + ')
) AS p
';

EXECUTE sp_executesql @cmd,N'@xml XML',@xml=@InputXml;
END
GO

然后像这样测试

declare @xml1 XML=
'<data>
  <PersonalInfo>
    <Person>
      <FirstName>Bob</FirstName>
      <LastName>Smith</LastName>
    </Person>
    <Person>
      <FirstName>John</FirstName>
      <LastName>Doe</LastName>
    </Person>
  </PersonalInfo>
</data>';


EXEC TransformPlainXML @xml1;

declare @xml2 XML=
'<data>
  <AddressInfo>
    <Address>
      <City>Cleveland</City>
      <State>OH</State>
    </Address>
    <Address>
      <City>Chicago</City>
      <State>IL</State>
    </Address>
  </AddressInfo>
</data>';
EXEC TransformPlainXML @xml2;

两个结果:

RowIndex    FirstName   LastName
1           Bob         Smith
2           John        Doe

和

RowIndex    City        State
1           Cleveland   OH
2           Chicago     IL

关于sql-server - sql中openxml的动态列和记录，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/34772503/

sql-serversqlltgt39xmlsql-server-openxml

有关sql-server - sql中openxml的动态列和记录的更多相关文章

  1. Hive SQL 五大经典面试题 - 2

    目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类

  2. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  3. ruby - 在 Ruby 中动态创建数组 - 2

    有没有办法在Ruby中动态创建数组？例如，假设我想遍历用户输入的书籍数组:books=gets.chomp用户输入:"TheGreatGatsby,CrimeandPunishment,Dracula,Fahrenheit451,PrideandPrejudice,SenseandSensibility,Slaughterhouse-Five,TheAdventuresofHuckleberryFinn"我把它变成一个数组:books_array=books.split(",")现在，对于用户输入的每一本书，我想用Ruby创建一个数组。伪代码来做到这一点:x=0books_array.

  4. ruby - 是否可以将 IRB 提示配置为动态更改？ - 2

    我想在IRB中浏览文件系统并让提示更改以反射(reflect)当前工作目录，但我不知道如何在每个命令后进行提示更新。最终，我想在日常工作中更多地使用IRB，让bash溜走。我在我的.irbrc中试过这个:require'fileutils'includeFileUtilsIRB.conf[:PROMPT][:CUSTOM]={:PROMPT_N=>"\e[1m:\e[m",:PROMPT_I=>"\e[1m#{pwd}>\e[m",:PROMPT_S=>"FOO",:PROMPT_C=>"\e[1m#{pwd}>\e[m",:RETURN=>""}IRB.conf[:PROMPT_MO

  5. ruby-on-rails - carrierwave:在序列化动态属性上安装 uploader - 2

    首先，我使用的是rails3.1.3和来自master的carrierwavegithub仓库的分支。我使用after_init钩子(Hook)来确定基于属性的字段页面模型实例并为这些字段定义属性访问器将值存储在序列化哈希中(希望它清楚我是什么谈论)。这是我正在做的事情的精简版:classPage省略mount_uploader命令让我可以访问我想要的属性。但是当我安装uploader时出现错误消息说“nil类的未定义新方法”我在源代码中读到有方法read_uploader和扩展模块中的write_uploader。我如何必须覆盖这些来制作mount_uploader命令使用我的“虚拟

  6. sql - 在 Rails Console for PostgreSQL 的表中显示数据 - 2

    我找到了这样的东西:Rails:Howtolistdatabasetables/objectsusingtheRailsconsole?这一行没问题:ActiveRecord::Base.connection.tables并返回所有表但是ActiveRecord::Base.connection.table_structure("users")产生错误:ActiveRecord::Base.connection.table_structure("projects")我认为table_structure不是Postgres方法。如何列出Postgres数据库的Rails控制台中表中的所有

  7. ruby-on-rails - rails : uninitialized constant just happen on production server - 2

    我有一个放在lib/network中的类:moduleNetworkApiclassNetworkProxyendend然后在另一个类中，我引用了这个类:network_proxy=::NetworkApi::NetworkProxy.new(params)一切都在我的开发环境中正常运行，但是当我部署到服务器时，我在上面一行收到错误消息:NameError:uninitializedconstantNetworkApi::NetworkProxy我不知道为什么会出现这个奇怪的错误。请告诉我为什么。 最佳答案 请注意Rails5dis

  8. ruby - 在 Ruby 中动态生成多维数组 - 2

    我正在尝试动态构建一个多维数组。我想要的基本上是这样的(为简单起见写出来):b=0test=[[]]test[b]这给了我错误:NoMethodError:undefinedmethod`test=[[],[],[]]而且它工作正常，但在我的实际使用中，我不会事先知道需要多少个数组。有一个更好的方法吗？谢谢 最佳答案 不需要像您正在使用的索引变量。只需将每个数组附加到您的test数组:irb>test=[]=>[]irb>test[["a","b","c"]]irb>test[["a","b","c"],["d","e","f"]]

  9. ruby-on-rails - 使用 gmaps4rails 动态加载谷歌地图标记 - 2

    如何只加载map边界内的标记gmaps4rails？当然，在平移和/或缩放后加载新的。与此直接相关的是，如何获取map的当前边界和缩放级别？ 最佳答案 我是这样做的，我只在用户完成平移或缩放后替换标记，如果您需要不同的行为，请使用不同的事件监听器:在你看来(index.html.erb):{"zoom"=>15,"auto_adjust"=>false,"detect_location"=>true,"center_on_user"=>true}},false,true)%>在View的底部添加:functiongmaps4rail

  10. ruby - 动态方法链？ - 2

    如何在对象上调用方法名称的嵌套哈希？例如，给定以下哈希:hash={:a=>{:b=>{:c=>:d}}}我想创建一个方法，给定上面的散列，执行以下操作:object.send(:a).send(:b).send(:c).send(:d)我的想法是我需要从一个未知的关联中获取一个特定的属性(这个方法不知道，但程序员知道)。我希望能够指定一个方法链来以嵌套哈希的形式检索该属性。例如:hash={:manufacturer=>{:addresses=>{:first=>:postal_code}}}car.execute_method_hash(hash)=>90210

随机推荐