Início / dba / Perguntas / 130740
Accepted
Sky
Asked: 2016-02-29 16:56:34 +0800 CST

A seleção de uma tabela contém uma coluna XML

  • 772

Recebi um arquivo do Excel (versão 2010) e, com base no requisito, fui solicitado a salvá-lo como um arquivo XML e, em seguida, importar para os dados SQL prontos para serem consultados em instruções SELECT normais.

Abaixo está a lista de etapas que segui, mas agora que tenho um SELECT nos dados, nenhum registro é retornado. Não tenho certeza se é devido à conversão que fiz para obter um arquivo XML de um arquivo do Excel ou se há um problema na minha OPENXMLconsulta.

  1. Para obter um XMLarquivo do .xlsxarquivo, abri o arquivo no EXCEL 2010 e salvei em 'Planilha XML 2003 (*.XML)'.

  2. Importei o arquivo XML em uma tabela utilizando o código abaixo:

    INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'C:\DEV\TestXML.xml', SINGLE_BLOB) AS x;


SELECT * FROM XMLwithOpenXML

insira a descrição da imagem aqui 1. Os dados XML foram armazenados agora na tabela, então eu uso o código abaixo para lê-los em uma instrução SELECT para acessar as colunas individuais:

    DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)


 SELECT @XML = XMLData FROM XMLwithOpenXML

    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


    SELECT SNo,Salutation,PatientNRIC,FirstName,LastName,Gender,Race
    FROM OPENXML(@hDoc, 'ROOT/SNo/Salutation/PatientNRIC/FirstName/LastName/Gender/Race')
    WITH 
    (
        SNo [nvarchar](50) '@SNo',
        Salutation [nvarchar](100) '@Salutation',
        PatientNRIC [nvarchar](255) '@PatientNRIC',
        FirstName [nvarchar](255) '@FirstName',
        LastName [nvarchar](255) '@LastName',
        Gender [nvarchar](255) '@Gender',
        Race [nvarchar](255) '@Race'
    )

Então

EXEC sp_xml_removedocument @hDoc
    GO

No entanto, o que recebo é apenas o nome das colunas, os dados reais não foram retornados:

insira a descrição da imagem aqui

Sou totalmente novo no comentário OPENXML e no trabalho com arquivos XML, tentei ler postagens diferentes, mas ainda não consegui descobrir por que nenhum dado é mostrado em minha consulta selecionada. Qualquer ajuda é apreciada.

sql-server import

1 respostas

  1. Best Answer

    Como seu XML é mantido em uma tabela, você pode usar os métodos do tipo de dados XML (por exemplo, .nodes, .value, .query etc). O que você pode ter um pouco de dificuldade são os namespaces em XML. Comece aqui para saber mais sobre eles: Entendendo Namespaces XML .

    Aqui estão alguns exemplos de consultas que fiz em uma planilha semelhante à sua. Experimente-os e trabalhe na Escada conforme sugerido por @MikaelEriksson e veja se eles começam a fazer sentido.

    IF OBJECT_ID('tempdb.#tmp') IS NOT NULL
    DROP TABLE #tmp
GO


-- 1) Pull the data "as is" unpivoted, tag it with row/cell numbers
-- and optionally pivot it
;WITH XMLNAMESPACES ( 
    DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
    'urn:schemas-microsoft-com:office:spreadsheet' AS ss   
    ), cte AS (
SELECT
    t.rawXMLId,
    ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) rowNumber,
    r.c.query('.') rowXML
FROM XMLwithOpenXML t
    CROSS APPLY XMLData.nodes('Workbook/Worksheet[@ss:Name = "Sheet1"]') w(c)
        CROSS APPLY w.c.nodes('Table/Row') r(c)
)
SELECT
    c.rawXMLId,
    c.rowNumber,
    ROW_NUMBER() OVER ( PARTITION BY rowNUmber ORDER BY rowNumber ) cellNumber,
    d.c.value('.', 'VARCHAR(50)') AS cellData
INTO #tmp
FROM cte c
    CROSS APPLY c.rowXML.nodes('Row/Cell/Data') d(c)
GO


SELECT 'unpivoted' s, *
FROM #tmp

SELECT 
    'pivoted' s,
    rawXMLId, rowNumber, 
    [1] AS Sno,
    [2] AS Salutation,
    [3] AS PatientNRIC,
    [4] AS FirstName,
    [5] AS LastName,
    [6] AS Gender,
    [7] AS Race
FROM #tmp
PIVOT ( MAX(cellData) For cellNumber In ( [1], [2], [3], [4], [5], [6], [7] )  ) upvt
GO



-- 2) Use your knowledge about the data to specify the columns manually
;WITH XMLNAMESPACES ( 
    DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
    'urn:schemas-microsoft-com:office:spreadsheet' AS ss   
    )
SELECT 
    t.rawXMLId,
    d.c.value('(Cell/Data/text())[1]', 'VARCHAR(50)') AS Sno,
    d.c.value('(Cell/Data/text())[2]', 'VARCHAR(50)') AS Salutation,
    d.c.value('(Cell/Data/text())[3]', 'VARCHAR(50)') AS PatientNRIC,
    d.c.value('(Cell/Data/text())[4]', 'VARCHAR(50)') AS FirstName,
    d.c.value('(Cell/Data/text())[5]', 'VARCHAR(50)') AS LastName,
    d.c.value('(Cell/Data/text())[6]', 'VARCHAR(50)') AS Gender,
    d.c.value('(Cell/Data/text())[7]', 'VARCHAR(50)') AS Race

FROM XMLwithOpenXML t
    CROSS APPLY XMLData.nodes('Workbook/Worksheet[@ss:Name = "Sheet1"]') w(c)
        --CROSS APPLY w.c.nodes('Table/Row') d(c)                   <-- include row header
        CROSS APPLY w.c.nodes('Table/Row[position() > 1]') d(c)     -- exclude row header

GO

    Meus resultados:

    Resultado dos testes

    Apenas uma observação sobre OPENXML, pode ser bom para partes maiores de XML, mas eu tento evitá-lo, se possível, devido a problemas de memória bem conhecidos. Seu exemplo particular não está funcionando por causa dos namespaces e você especificou o caminho errado (Root etc). Poste de volta se você realmente deseja trabalhar com o OPENXML, mas eu desaconselharia isso neste exemplo, pois seus dados são mantidos em uma tabela - OPENXMLsó pode funcionar com um pedaço de XML por vez.

    • 2

relate perguntas