gpt4 book ai didi

sql-server - SQL Server 2005查询XML时,文档的多个标签在同一行

转载 作者:数据小太阳 更新时间:2023-10-29 01:56:49 25 4
gpt4 key购买 nike

更新以提供澄清

这是我遇到问题的其中一个 xml 文件的完整副本。

<Grower_Run xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Puller xsi:type="Puller">
<Puller_Number xsi:type="xsd:int">16</Puller_Number>
</Puller>
<Run_ID xsi:type="xsd:string">SA1611030B</Run_ID>
<Crucible xsi:type="Crucible">
<Crucible_Type xsi:type="xsd:string">RWNTYPE</Crucible_Type>
<Section>
<Grower_Run_Section>
<SectionID xsi:type="xsd:string">SA1611030B1</SectionID>
<Crystal_Growth>
<Growth_StartTime xsi:type="xsd:dateTime">2011-03-01T12:59:30</Growth_StartTime>
<Growth_Process>
<Growth_Process>
<Process_Name xsi:type="xsd:string">BODY</Process_Name>
<Single_Moment_Snapshot>
<Single_Moment_Snapshot>
<Snapshot_Name xsi:type="xsd:string">Body Start</Snapshot_Name>
<Snapshot_Datetime xsi:type="xsd:dateTime">2011-03-01T17:11:30</Snapshot_Datetime>
<Ingot_Length xsi:type="xsd:decimal">228.19</Ingot_Length>
<Heater_Temp xsi:type="xsd:decimal">1337.09</Heater_Temp>
<LS_Temp xsi:type="xsd:decimal">1243.00</LS_Temp>
<Heater_Power xsi:type="xsd:decimal">56.10</Heater_Power>
</Single_Moment_Snapshot>
<Single_Moment_Snapshot>
<Snapshot_Name xsi:type="xsd:string">Mid Body</Snapshot_Name>
<Snapshot_Datetime xsi:type="xsd:dateTime">2011-03-01T17:11:30</Snapshot_Datetime>
<Ingot_Length xsi:type="xsd:decimal">228.19</Ingot_Length>
<Heater_Temp xsi:type="xsd:decimal">1337.09</Heater_Temp>
<LS_Temp xsi:type="xsd:decimal">1243.00</LS_Temp>
<Heater_Power xsi:type="xsd:decimal">56.10</Heater_Power>
</Single_Moment_Snapshot>
<Single_Moment_Snapshot>
<Snapshot_Name xsi:type="xsd:string">Tail Start</Snapshot_Name>
<Snapshot_Datetime xsi:type="xsd:dateTime">2011-03-02T01:34:24</Snapshot_Datetime>
<Ingot_Length xsi:type="xsd:decimal">230.40</Ingot_Length>
<Heater_Temp xsi:type="xsd:decimal">1338.20</Heater_Temp>
<LS_Temp xsi:type="xsd:decimal">1243.40</LS_Temp>
<Heater_Power xsi:type="xsd:decimal">56.39</Heater_Power>
</Single_Moment_Snapshot>
</Single_Moment_Snapshot>
</Growth_Process>
</Growth_Process>
<Growth_FinishTime xsi:type="xsd:dateTime">2011-03-02T01:35:24</Growth_FinishTime>
</Crystal_Growth>
</Grower_Run_Section>
<Grower_Run_Section>
<SectionID xsi:type="xsd:string">SA1611030B9</SectionID>
<Crystal_Growth>
<Growth_StartTime xsi:type="xsd:dateTime">2011-03-02T04:02:37</Growth_StartTime>
<Growth_Process>
<Growth_Process>
<Process_Name xsi:type="xsd:string">BODY</Process_Name>
<Single_Moment_Snapshot>
<Single_Moment_Snapshot>
<Snapshot_Name xsi:type="xsd:string">Body Start</Snapshot_Name>
<Snapshot_Datetime xsi:type="xsd:dateTime">2011-03-02T07:54:39</Snapshot_Datetime>
<Ingot_Length xsi:type="xsd:decimal">231.80</Ingot_Length>
<Heater_Temp xsi:type="xsd:decimal">1340.00</Heater_Temp>
<LS_Temp xsi:type="xsd:decimal">1246.70</LS_Temp>
<Heater_Power xsi:type="xsd:decimal">56.60</Heater_Power>
</Single_Moment_Snapshot>
<Single_Moment_Snapshot>
<Snapshot_Name xsi:type="xsd:string">Mid Body</Snapshot_Name>
<Snapshot_Datetime xsi:type="xsd:dateTime">2011-03-02T07:54:39</Snapshot_Datetime>
<Ingot_Length xsi:type="xsd:decimal">231.80</Ingot_Length>
<Heater_Temp xsi:type="xsd:decimal">1340.00</Heater_Temp>
<LS_Temp xsi:type="xsd:decimal">1246.70</LS_Temp>
<Heater_Power xsi:type="xsd:decimal">56.60</Heater_Power>
</Single_Moment_Snapshot>
<Single_Moment_Snapshot>
<Snapshot_Name xsi:type="xsd:string">Tail Start</Snapshot_Name>
<Snapshot_Datetime xsi:type="xsd:dateTime">2011-03-03T06:47:19</Snapshot_Datetime>
<Ingot_Length xsi:type="xsd:decimal">1778.00</Ingot_Length>
<Heater_Temp xsi:type="xsd:decimal">1388.80</Heater_Temp>
<LS_Temp xsi:type="xsd:decimal">1330.70</LS_Temp>
<Heater_Power xsi:type="xsd:decimal">63.50</Heater_Power>
</Single_Moment_Snapshot>
</Single_Moment_Snapshot>
</Growth_Process>
</Growth_Process>
<Growth_FinishTime xsi:type="xsd:dateTime">2011-03-03T06:48:19</Growth_FinishTime>
</Crystal_Growth>
</Grower_Run_Section>
</Section>
</Crucible>
</Grower_Run>

好的,为了澄清,这里是我的完整查询。

DECLARE @FILES TABLE(FILENAME VARCHAR(20),DEPTH INT,FILE_FLAG INT)
DECLARE @XML_TABLE TABLE(
xmlFileName VARCHAR(300),
xml_data xml
)
DECLARE @xmlFileName VARCHAR(300)
DECLARE @FILENAME VARCHAR(20)

INSERT INTO @FILES
EXEC Master.dbo.xp_DirTree '\\SASSOAPPSRV\Grower XML Files\',1,1

DECLARE XML_CURSOR CURSOR FOR SELECT FILENAME FROM @FILES

OPEN XML_CURSOR
FETCH NEXT FROM XML_CURSOR
INTO @FILENAME

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @xmlFileName = '\\SASSOAPPSRV\Grower XML Files\' + @FILENAME

INSERT INTO @XML_TABLE(xmlFileName, xml_data)
EXEC('
SELECT ''' + @xmlFileName + ''', xmlData
FROM
(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
FETCH NEXT FROM XML_CURSOR
INTO @FILENAME

END
CLOSE XML_CURSOR
DEALLOCATE XML_CURSOR

DECLARE @PARSED_XML TABLE(
S VARCHAR(200),
RUN_ID VARCHAR(20)
,SECTION_ID VARCHAR(50)
)



INSERT INTO @PARSED_XML
SELECT
T.xmlFileName AS S,
t.xml_data.value('(/Grower_Run/Run_ID)[1]', 'varchar(50)') AS 'RunID',
Section.value('(Grower_Run_Section/SectionID)[1]', 'varchar(50)') as 'SectionID'
FROM
@xml_table t
CROSS APPLY
t.xml_data.nodes('/Grower_Run/Crucible/Section') AS Tmp(Section)

SELECT
S
,RUN_ID
,SECTION_ID
FROM @PARSED_XML
WHERE RUN_ID = 'SA1611030B'
ORDER BY RUN_ID

这就是我得到的结果。

\\SASSOAPPSRV\Grower XML Files\SA1611030B.xml   SA1611030B  SA1611030B1

这是个问题,因为我需要在不同的行中查看来自节点的两个副本的数据。


所以我有很多具有以下结构的 XML 文档

<Grower_Run xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Run_ID xsi:type="xsd:string">SA0111023B</Run_ID>
<Crucible xsi:type="Crucible">
<Section>
<Grower_Run_Section>
<SectionID xsi:type="xsd:string">SA0111023B1</SectionID>
</Grower_Run_Section>
</Section>
<Section>
<Grower_Run_Section>
<SectionID xsi:type="xsd:string">SA0111023B9</SectionID>
</Grower_Run_Section>
</Section>
</Crucible>
</Grower_Run>

我已经导入到一个临时表的 XML 字段中,我正在使用这样的查询对其进行查询

SELECT
CAST(XML_DATA.query('data(/Grower_Run/Run_ID)') AS VARCHAR(20)) AS [RUN ID]
,CAST(XML_DATA.query('data(/Grower_Run/Crucible/Section/Grower_Run_Section/SectionID)') AS VARCHAR(50)) AS [SECTION ID]
FROM @XML_TABLE

所以问题是我从“SectionID”标签的多个实例中得到的结果显示在同一行中。

   RunID    |   SectionID
--------------------------
SA0111023B | SA0111023B1 SA0111023B9

应该是什么时候

RunID       |   SectionID
-----------------------------
SA0111023B | SA0111023B1
SA0111023B | SA0111023B9

如果我有 [1] 到这样的 xml 查询结束

CAST(XML_DATA.query('data(/Grower_Run/Crucible/Section/Grower_Run_Section/SectionID)')[1]

它会将第二个部分 ID 一起删除,这不起作用,因为我需要两个。

有什么帮助吗?

最佳答案

如果你想从 XML 文档中选择出多个“行”,你需要使用 .nodes() SQL Server XML 函数 - 像这样:

SELECT 
@XMLTable.value('(/Grower_Run/Run_ID)[1]', 'varchar(50)') AS 'RunID',
Section.value('(Grower_Run_Section/SectionID)[1]', 'varchar(50)') as 'SectionID'
FROM
@XMLTable.nodes('/Grower_Run/Crucible/Section') AS Tmp(Section)

FROM 中的 XPath 语句子句基本上定义了一个基于 XPath 的 XML 元素的“伪表”。所以这里你得到每个 <Section> 的伪表XML 中的条目 - 然后您可以使用 .value() 从中选择单个元素功能。

如果您想从包含类型为 XML 的列的表中选择它,您可能需要查看 CROSS APPLY命令:

SELECT 
t.SomeColumn,
t.XmlColumn.value('(/Grower_Run/Run_ID)[1]', 'varchar(50)') AS 'RunID',
Section.value('(Grower_Run_Section/SectionID)[1]', 'varchar(50)') as 'SectionID'
FROM
dbo.YourTable t
CROSS APPLY
t.XmlColumn.nodes('/Grower_Run/Crucible/Section') AS Tmp(Section)

关于sql-server - SQL Server 2005查询XML时,文档的多个标签在同一行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/5251065/

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