gpt4 book ai didi

sql-server-2005 - 从 XML 文件中选择数据作为 SQL 中的表

转载 作者:行者123 更新时间:2023-12-03 01:29:24 25 4
gpt4 key购买 nike

有人可以向我展示一些用于查询 xml 文件的 TSQL,就好像它是一个表一样吗?

该文件位于服务器上,“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)。

我认为了解这一点很有用,所以我在 Google 上搜索并阅读了很多帖子,直到找到 this one.

更新从文件加载:

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

选择@xmlData

关于sql-server-2005 - 从 XML 文件中选择数据作为 SQL 中的表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7649301/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com