将多个 XML 元素切碎为多个列

Question

我正在尝试将此xml列粉碎为多个xml列，但它的格式似乎错误。

这是我使用 xml 路径(( 的数据

<claim id="1111111">
  <InsHistDB>2</InsHistDB>
  <ClaimHistID>111111</ClaimHistID>
  <PatID>00000001</PatID>
  <ProcedureData>
    <row proc_logid="0000009" proc_logdb="1000000" createdate="2000-09-21T00:00:00" pldate="2000-09-21T00:00:00" adacode="D0120" />
    <row proc_logid="1211557" proc_logdb="1000010" createdate="2015-09-21T00:00:00" pldate="2015-09-21T00:00:00" adacode="D0220" />
    <row proc_logid="1211558" proc_logdb="1000010" createdate="2015-09-21T00:00:00" pldate="2015-09-21T00:00:00" adacode="D0230" />
    <row proc_logid="1211556" proc_logdb="1000010" createdate="2015-09-21T00:00:00" pldate="2015-09-21T00:00:00" adacode="D0272" />
  </ProcedureData>
</claim>

选择语句当前是这个

SELECT TOP (1000)  [ClaimID] as '@id'
      ,[InsHistDB]
      ,[ClaimHistID]
      ,[PatID]
   -- ,[ProcedureData].value('declare namespace ns= "ProcedureData"; (/ns:ProcedureData/ns:row[1])','nvarchar(50)') as pp
     ,[ProcedureData]
     --,[ProcedureData].query('proc_logid').value('.','varchar(50)') as 'proc_1'
  FROM [Mine].[dbo].[claim]
  where claimid=1111111
 FOR XML PATH('claim')

我想做的是将 {Proc_Log_id} 分成不同的列，以便该行应该读取。

Claim ID         INSHISTDB       CLaimHistID       PATID      Proc_Id1      Proc_ID2      ProcID3   procID4
 11111              2               1111111      000000001     0000009    1211557        1211558      1211556                   

这可能还是我只是在旋转我的轮子？此外，这将适用于多个 patid，因此需要没有 where 子句的查询。此外，我相信每个xml数据点最多可以有10个proc_logids。我对空值很好，因为我计划透视和规范化这些数据。

感谢您的阅读。

Answer 1

选择第三个节点的语法是 column.value('(/foo/bar)[3]','varchar(25)') 。

您考虑其他选择是正确的。特别是"查询"或"节点"函数更适合，因为它们可以将值作为第二个结果集或新的 XML 文档返回。

Answer 2

您可以首先绘制 XML 内容中的 ID，然后必须使用您提到的 PIVOT。由于您不确定列数，因此可以使用动态透视来使其更加灵活。

首先使用 XML 节点和值获取数据，我将值存储在临时表中，因为这样做很方便。

Declare @xmlstring xml =  
'<claim id="1111111">
  <InsHistDB>2</InsHistDB>
  <ClaimHistID>111111</ClaimHistID>
  <PatID>00000001</PatID>
  <ProcedureData>
    <row proc_logid="0000009" proc_logdb="1000000" createdate="2000-09-21T00:00:00" pldate="2000-09-21T00:00:00" adacode="D0120" />
    <row proc_logid="1211557" proc_logdb="1000010" createdate="2015-09-21T00:00:00" pldate="2015-09-21T00:00:00" adacode="D0220" />
    <row proc_logid="1211558" proc_logdb="1000010" createdate="2015-09-21T00:00:00" pldate="2015-09-21T00:00:00" adacode="D0230" />
    <row proc_logid="1211556" proc_logdb="1000010" createdate="2015-09-21T00:00:00" pldate="2015-09-21T00:00:00" adacode="D0272" />
  </ProcedureData>
</claim>' 
if object_id('tempdb..#temp1') is not null 
drop table #temp1
select m.Col.value('@id','varchar(150)') as ClaimID
,m.Col.value('(InsHistDB)[1]','varchar(150)') as InsHistDB 
,m.Col.value('(ClaimHistID)[1]','varchar(150)') as ClaimHistID 
,m.Col.value('(PatID)[1]','varchar(150)') as PatID
,t.new.value('(@proc_logid)[1]', 'Varchar(150)') IDcol, 
concat('Proc_ID', cast(ROW_NUMBER() over (Partition by m.Col.value('@id','varchar(150)') order by t.new.value('(@proc_logid)[1]', 'Varchar(150)')) as varchar(10))) AS ProcID 
into #temp1   
from @xmlstring.nodes('/claim') as m(col)
CROSS APPLY @xmlstring.nodes('claim/ProcedureData/row') as t(new);

您将在临时表中拥有这样的数据。我已经将给定的claimID的行号与proc ID连接起来，以按照您提到的方式创建列。

ClaimID    InsHistDB    ClaimHistID PatID   IDcol   ProcID
1111111       2          111111    00000001 0000009 Proc_ID1
1111111       2          111111    00000001 1211557 Proc_ID2
1111111       2          111111    00000001 1211558 Proc_ID3
1111111       2          111111    00000001 1211556 Proc_ID4

然后，您可以使用动态透视来获得预期的输出。

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.procID) 
        FROM #temp1 c
        FOR XML PATH(''))  
    ,1,1,'')

    set @query = 'SELECT ClaimID, InsHistDB,ClaimHistID, PatID,  '+@cols+'  from 
        (
            select ClaimID, InsHistDB,ClaimHistID, PatID, procID, IDcol  
                from #temp1
       ) x
        pivot 
        (
             max(IDcol)
            for procID in (' + @cols + ')
        ) p '
Exec sp_executesql @query

查询的最终输出：

ClaimID InsHistDB   ClaimHistID PatID   Proc_ID1    Proc_ID2    Proc_ID3    Proc_ID4
1111111     2        111111    00000001 0000009     1211557     1211558     1211556