时间戳

首页 > 解决方案 > 我有以下 XML，我需要将其存储在 sql 表中

sql - 我有以下 XML，我需要将其存储在 sql 表中

问题描述

我需要标签 ServerName、DBName、UserName、RoleName。我无法访问这些标签，因为 xml 的第一部分给我带来了麻烦

<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>Selected.System.Object</T>
      <T>System.Management.Automation.PSCustomObject</T>
      <T>System.Object</T>
    </TN>
    <MS>
      <S N="ServerName">DDW</S>
      <S N="DBName">dwSeriesOLAP</S>
      <S N="UserName">123Z</S>
      <S N="RoleName">test1</S>
    </MS>
  </Obj>
  <Obj RefId="1">
   <TNRef RefId="0" />
    <MS>
      <S N="ServerName">DDW</S>
      <S N="DBName">dwSeriesOLAP</S>
      <S N="UserName">1234</S>
      <S N="RoleName">test2</S>
    </MS>
  </Obj>
  <Obj RefId="2">
    <TNRef RefId="0" />
    <MS>
      <S N="ServerName">DDW</S>
      <S N="DBName">dwSeriesOLAP</S>
      <S N="UserName">12345aa</S>
      <S N="RoleName">test3</S>
    </MS>
   </Obj>
</Objs>

标签： sqlxmldatabasevb.net

解决方案

在您的 XML 中有两种不同的结构：

DECLARE @xml XML=
N'<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>Selected.System.Object</T>
      <T>System.Management.Automation.PSCustomObject</T>
      <T>System.Object</T>
    </TN>
    <MS>
      <S N="ServerName">DDW</S>
      <S N="DBName">dwSeriesOLAP</S>
      <S N="UserName">123Z</S>
      <S N="RoleName">test1</S>
    </MS>
  </Obj>
  <Obj RefId="1">
   <TNRef RefId="0" />
    <MS>
      <S N="ServerName">DDW</S>
      <S N="DBName">dwSeriesOLAP</S>
      <S N="UserName">1234</S>
      <S N="RoleName">test2</S>
    </MS>
  </Obj>
  <Obj RefId="2">
    <TNRef RefId="0" />
    <MS>
      <S N="ServerName">DDW</S>
      <S N="DBName">dwSeriesOLAP</S>
      <S N="UserName">12345aa</S>
      <S N="RoleName">test3</S>
    </MS>
   </Obj>
</Objs>';

--查询你的第一个元素<TN>

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/powershell/2004/04')
SELECT obj.value('@RefId','int') AS ObjectId
      ,obj.value('(TN/@RefId)[1]','int') AS TNId
      ,t.value('text()[1]','nvarchar(max)') AS T_Content
FROM @xml.nodes('/Objs/Obj[TN]') A(obj) --<-- see the predicate for `[TN]`
CROSS APPLY A.obj.nodes('TN/T') B(t);

--如果你可以确定，前一个的替代方案，只有一个<TN>元素：

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/powershell/2004/04')
SELECT @xml.value('(/Objs/Obj[TN]/@RefId)[1]','int') AS ObjectId
      ,@xml.value('(/Objs/Obj[TN]/TN/@RefId)[1]','int') AS TNId
      ,t.value('text()[1]','nvarchar(max)') AS T_Content
FROM @xml.nodes('/Objs/Obj[TN]/TN/T') A(t);

--重复<MS><S>元素的查询

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/powershell/2004/04')
SELECT obj.value('@RefId','int') AS ObjectId
      --if appropriate you might use COALLESCE for the TN-ids
      ,obj.value('(TN/@RefId)[1]','int') AS TNId
      ,obj.value('(TNRef/@RefId)[1]','int') AS TNRefId
      ,obj.value('(MS/S[@N="ServerName"]/text())[1]','nvarchar(max)') AS ServerName
      ,obj.value('(MS/S[@N="DBName"]/text())[1]','nvarchar(max)') AS DBName
      ,obj.value('(MS/S[@N="UserName"]/text())[1]','nvarchar(max)') AS UserName
      ,obj.value('(MS/S[@N="RoleName"]/text())[1]','nvarchar(max)') AS RoleName
FROM @xml.nodes('/Objs/Obj') A(obj)

--还有一个单独检索每个嵌入式结构。这允许逐步的方法（例如，使用中间临时表）。

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/powershell/2004/04')
,TheObjects AS
(
    SELECT obj.value('@RefId','int') AS ObjectId
          ,obj.query('TN') AS TN_Element 
          ,obj.query('TNRef') AS TNRef_Element 
          ,obj.query('MS') AS MS_Element 
    FROM @xml.nodes('/Objs/Obj') A(obj)
)
SELECT * 
FROM TheObjects;

简而言之：

  • 我们需要声明现有的默认命名空间
  • 我们.nodes()用来潜入重复元素
  • 我们用于.value()检索数据（@xml直接来自或来自来自的 Xml 片段.nodes()）。
  • 我们.query()用来检索 Xml 片段。

推荐阅读