如何在SQL Server表中将XML从一种格式写入另一种格式？

I have a problem. I have a SQL Server table that stores a bunch of XML documents in a column. I need to pass these XML documents to a XML parser, but the format of my starting XML is not in a format the parser can accept. Here's what I have to work with -

我有个问题。我有一个SQL Server表，它在一列中存储了一堆XML文档。我需要将这些XML文档传递给XML解析器，但我的起始XML的格式不是解析器可以接受的格式。这是我必须与之合作的 -

XML stored in my SQL Server table is in the following format:

存储在我的SQL Server表中的XML采用以下格式：

<Document ID="207">
  <Version>1.0</Version>
  <LastModifiedInVersion>1.0</LastModifiedInVersion>
  <Signatures />
  <Controls>
    <Control ID="EmpID">
      <Value>45678</Value>
    </Control>
    <Control ID="EmpFN">
      <Value>Ryn</Value>
    </Control>
    <Control ID="EmpLN">
      <Value>Veris</Value>
    </Control>
    <Control ID="EmpDOB">
      <Value>01/19/1980</Value>
    </Control>
  </Controls>
  <AutoKeys />
</Document>

I need to take that XML and make it look like this:

我需要使用该XML并使其看起来像这样：

<xml_record>
<employee>
    <EmpID value="45678"/>
    <EmpFN value="Ryn"/>
    <EmpLN value="Veris"/>
    <empDOB value="01/19/1980"/>
</employee>
</xml_record>

I looked into using XSLT, but it seems to be all based around displaying XML data in a browser and not a translation of the actual format. My ultimate goal is simply to translate the format and use the XML parser to pull the employee values to populate another table, I don't need any of the rest of the original XML. Is what I'm looking to do even possible? If so just pointing me in the right direction would be great.

我研究过使用XSLT，但它似乎都是基于在浏览器中显示XML数据而不是实际格式的翻译。我的最终目标是简单地翻译格式并使用XML解析器来提取员工值以填充另一个表，我不需要任何其他原始XML。我想要做甚么可能吗？如果是这样，只要指出我正确的方向就会很棒。

1 个解决方案

#1

Try this:

尝试这个：

DECLARE @xdoc xml = '<Document ID="207">
  <Version>1.0</Version>
  <LastModifiedInVersion>1.0</LastModifiedInVersion>
  <Signatures />
  <Controls>
    <Control ID="EmpID">
      <Value>45678</Value>
    </Control>
    <Control ID="EmpFN">
      <Value>Ryn</Value>
    </Control>
    <Control ID="EmpLN">
      <Value>Veris</Value>
    </Control>
    <Control ID="EmpDOB">
      <Value>01/19/1980</Value>
    </Control>
  </Controls>
  <AutoKeys />
</Document>
'


SELECT 
    EmpID   'EmpID/@value',
    EmpFN    'EmpFN/@value',
    EmpLN    'EmpLN/@value',
    EmpDOB   'EmpDOB/@value'
FROM (
SELECT 
    @xdoc.query('//Control[@ID = "EmpID"]/Value').value('.','INT') AS EmpID,
    @xdoc.query('//Control[@ID = "EmpFN"]/Value').value('.','VARCHAR(100)') AS EmpFN,
    @xdoc.query('//Control[@ID = "EmpLN"]/Value').value('.','VARCHAR(100)') AS EmpLN,
    @xdoc.query('//Control[@ID = "EmpDOB"]/Value').value('.','VARCHAR(100)') AS EmpDOB
)t
FOR XML PATH('employee'), ROOT('xml_record')

Not most elegant, but working.

不是最优雅，但工作。

#1