sql - 我想使用表格式的 SQL 查询从驱动器路径中读取 XML

Question

我想使用表格格式的 SQL 查询从驱动器路径中读取 XML。下面是我的 XML 代码:

在下面的代码中，我在从选项 id、QuestionID 拆分记录时遇到问题，如下面所附的 .PNG。

我有很多这种类型的 XML，我想直接将其转储到数据库中。

<Root>
  <DeviceType>A</DeviceType>
  <PregnancyId>1</PregnancyId>
  <Questions>
    <Question>
      <QuestionId>1</QuestionId>
      <QuestionInputTypeId>1</QuestionInputTypeId>
      <SubQuestionId>0</SubQuestionId>
      <Options>
        <Option>
          <DropdownOptionId>0</DropdownOptionId>
          <OptionId>5</OptionId>
          <OptionValue>Very good</OptionValue>
        </Option>
        <Option>
          <DropdownOptionId>0</DropdownOptionId>
          <OptionId>6</OptionId>
          <OptionValue>Very good</OptionValue>
        </Option>
      </Options>
    </Question>
    <Question>
      <QuestionId>2</QuestionId>
      <QuestionInputTypeId>1</QuestionInputTypeId>
      <SubQuestionId>0</SubQuestionId>
      <Options>
        <Option>
          <DropdownOptionId>0</DropdownOptionId>
          <OptionId>7</OptionId>
          <OptionValue>Life, in general</OptionValue>
        </Option>
      </Options>
    </Question>
  </Questions>
  <QuizTypeId>5</QuizTypeId>
  <UserId>10</UserId>
</Root>

我准备了以下查询但没有得到准确的输出。

DECLARE @XMLwithOpenXML TABLE
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)


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

SELECT * FROM @XMLwithOpenXML

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM @XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML



SELECT DeviceType,   PregnancyId,  QuestionId,   QuestionInputTypeId, 
SubQuestionId,       DropdownOptionId,    OptionId,     OptionValue,  
QuizTypeId,   UserId

FROM OPENXML(@hDoc, 'Root/DeviceType')
WITH 
(
DeviceType                        varchar(200) '../DeviceType',
PregnancyId                       varchar(200) '../PregnancyId',
QuestionId                        varchar(200) 'QuestionId',
QuestionInputTypeId        varchar(200) 'QuestionInputTypeId',
SubQuestionId              varchar(200) 'SubQuestionId',
DropdownOptionId           varchar(200) 'DropdownOptionId',
OptionId                          varchar(200) 'OptionId',
OptionValue                       varchar(200) 'OptionValue',
QuizTypeId                        varchar(200) 'QuizTypeId',
UserId                   varchar(200) 'UserId'
)
EXEC sp_xml_removedocument @hDoc
GO

我想要 SQL 中的输出如下:

Answer 1

您可以按如下方式尝试 XQuery:

WITH Src AS
(
    SELECT CAST(X as xml) XmlValue FROM OPENROWSET(BULK 'E:\Temp\File.xml', SINGLE_BLOB) T(X)
)
SELECT
    X.value('(../../../../DeviceType)[1]','char(1)') DeviceType,
    X.value('(../../../../PregnancyId)[1]','int') PregnancyId,
    X.value('(../../QuestionId)[1]','int') QuestionId,
    X.value('(../../SubQuestionId)[1]','int') SubQuestionId,
    X.value('(DropdownOptionId)[1]','int') DropdownOptionId,
    X.value('(OptionId)[1]','int') OptionId,
    X.value('(OptionValue)[1]','nvarchar(100)') OptionValue,
    X.value('(../../../../QuizTypeId)[1]','int') QuizTypeId,
    X.value('(../../../../UserId)[1]','int') UserId
FROM Src
CROSS APPLY XmlValue.nodes('Root/Questions/Question/Options/Option') T(X)