从 XML 文件中选择数据作为 TSQL 中的表 [英] Select data from XML file as table in TSQL

问题描述

有人能告诉我一些 TSQL 用来查询 xml 文件，就好像它是一个表一样吗?

Could someone show me some TSQL to use to query an xml file as if it were a table?

文件在服务器上，C:\xmlfile.xml"

The file is on the server, "C:\xmlfile.xml"

并包含

<ArrayOfSpangemansFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <SpangemansFilter>
        <FilterID>1219</FilterID>
        <Name>Fred</Name>
        <Code>510</Code>
        <Department>N</Department>
        <Number>305327</Number>
    </SpangemansFilter>
    <SpangemansFilter>
        <FilterID>3578</FilterID>
        <Name>Gary</Name>
        <Code>001</Code>
        <Department>B</Department>
        <Number>0692690</Number>
    </SpangemansFilter>
    <SpangemansFilter>
        <FilterID>3579</FilterID>
        <Name>George</Name>
        <Code>001</Code>
        <Department>X</Department>
        <Number>35933</Number>
    </SpangemansFilter>
</ArrayOfSpangemansFilter>

我所追求的示例输出

FilterID    |Name       |Code       |Department             |Number
-------------------------------------------------------------------
1219        |Fred       |510        |N                      |305327
3578        |Gary       |001        |B                      |0692690
3579        |George     |001        |X                      |35933

推荐答案

set @xmlData='<?xml version="1.0"?>
<ArrayOfSpangemansFilter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SpangemansFilter>
<FilterID>1219</FilterID>
<Name>Fred</Name>
<Code>510</Code>
<Department>N</Department>
<Number>305327</Number>
</SpangemansFilter>
<SpangemansFilter>
<FilterID>3578</FilterID>
<Name>Gary</Name>
<Code>001</Code>
<Department>B</Department>
<Number>0692690</Number>
</SpangemansFilter>
<SpangemansFilter>
<FilterID>3579</FilterID>
<Name>George</Name>
<Code>001</Code>
<Department>X</Department>
<Number>35933</Number>
</SpangemansFilter>
</ArrayOfSpangemansFilter>'


SELECT 
  ref.value('FilterID[1]', 'int') AS FilterID ,
  ref.value('Name[1]', 'NVARCHAR (10)') AS Name ,
  ref.value('Code[1]', 'NVARCHAR (10)') AS Code ,
  ref.value('Department[1]', 'NVARCHAR (3)') AS Department,
  ref.value('Number[1]', 'int') AS Number      
FROM @xmlData.nodes('/ArrayOfSpangemansFilter/SpangemansFilter') 
xmlData( ref )

产生:

FilterID    Name       Code       Department Number
----------- ---------- ---------- ---------- -----------
1219        Fred       510        N          305327
3578        Gary       001        B          692690
3579        George     001        X          35933

注意:需要 [1] 来指示您要选择序列的第一个值，因为查询可能每行返回一个以上的匹配值(假设您的 XML 包含多个 FilterID每个 SpangemansFilter).

Note: The [1] is needed to indicate that you want to select the first value of the sequence since the query may return more than one matched value per row (imagine your XML containing several FilterIDs per SpangemansFilter).

我认为知道这很有用，所以我用谷歌搜索并阅读了很多帖子，直到我找到 这个.

I thought this was useful to know, so I Googled and read many posts until I found this one.

更新从文件加载:

DECLARE @xmlData XML
SET @xmlData = (
  SELECT * FROM OPENROWSET (
    BULK 'C:\yourfile.xml', SINGLE_CLOB
  ) AS xmlData
)

选择@xmlData

这篇关于从 XML 文件中选择数据作为 TSQL 中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！