Ler dados XML no servidor SQL 2012

Question

Ler dados XML no servidor SQL 2012

quero ler dados xml no ficheiro xml. Eu tenho uma coluna de tabela consiste com os dados xml.

se carregar no ficheiro xml, irá abrir no estúdio de gestão de servidores Sql.
formato de ficheiro xml apresentado em baixo.

quero ler apenas NTDomainName, DatabaseName e ServerName e escrever esses dados na outra tabela. Formato da tabela abaixo

NTDomainName | DatabaseName | ServerName 
----------
ABC          | TestCube1    | SERXYZ

0

sql-server sql stored-procedures

Author: Serkan Arslan, 2018-03-02

Source

3 answers

score 1 · Answer 1

Tenta isto:

declare @xml xml
set @xml = '<event><data name="NTUserName"><value>MyName</value></data><data name="NTDomainName"><value>DomainName</value></data><data name="ServerName"><value>ServerName</value></data></event>'

select [NTDomainName], [DatabaseName], [ServerName] from
(
    select [name],[value] from (
        select c.value('./@name', 'varchar(100)') [name], c.value('(./value)[1]', 'varchar(100)') [value]
        from @xml.nodes('/event/data') as t(c)
    ) a where [name] in ('NTDomainName', 'DatabaseName', 'ServerName')
) x
pivot (
    max(value) for [name] in ([NTDomainName], [DatabaseName], [ServerName])
) as [pivot_Name]

A pesquisa mais interna irá obter informações do XML, uma linha para cada valor do atributo name que deseja obter. Então, a saída desta consulta precisa ser girada.

score 1 · Answer 2

Acho que procuras isto.

SELECT * FROM (
SELECT 
CAST(f.x.query('data(@name)') as varchar(150)) as data_name,
CAST(f.x.query('data(value)') as varchar(150)) as data_value
FROM @xml.nodes('/event') as t(n)
CROSS APPLY t.n.nodes('data') as f(x)) X
PIVOT (MAX(data_value) FOR data_name IN (NTDomainName, DatabaseName, ServerName)) as pvt

score 0 · Answer 3

Se não quiser utilizar PIVOT:

DECLARE @DataSource TABLE
(
    [ID] TINYINT IDENTITY(1,1)
   ,[XML] XML
);

INSERT INTO @DataSource ([XML])
VALUES ('<event><data name="SessionID">S1</data><data name="NTUserName">User1</data><data name="DatabaseName">DB1</data><data name="ServerName">SN1</data></event>')
      ,('<event><data name="SessionID">S1</data><data name="NTUserName">User2</data><data name="DatabaseName">DB2</data><data name="ServerName">SN2</data></event>');

SELECT [ID]
      ,MAX(CASE wHEN C.value('(./@name)[1]', 'varchar(50)')  = 'NTUserName' THEN C.value('(.)[1]', 'varchar(50)')  END) AS [NTUserName]
      ,MAX(CASE wHEN C.value('(./@name)[1]', 'varchar(50)')  = 'DatabaseName' THEN C.value('(.)[1]', 'varchar(50)')  END) AS [DatabaseName]
      ,MAX(CASE wHEN C.value('(./@name)[1]', 'varchar(50)')  = 'ServerName' THEN C.value('(.)[1]', 'varchar(50)')  END) AS [ServerName]
FROM @DataSource
CROSS APPLY [XML].nodes('event/data[@name = "NTUserName" or @name = "DatabaseName" or @name = "ServerName"]') T(c)
GROUP BY [ID];