查询以从 XML 读取数据

Question

这里已经有一些类似的问题，但它们似乎对我不起作用。我需要查询一个 XML 文件以从中提取数据，以便我们可以将查询结果用于其他目的。我已经在网上搜索了一种方法来执行此操作，似乎值（）方法是推荐的方法。我用下面的代码尝试了这个：

DECLARE @x XML
SET @x = 
'<?xml version="1.0" encoding="UTF-8"?>
<BlockOrderMessage xmlns="http://www.test.com/production/block"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.test.com/production/block file:/C:/Users/test.xsd"
BlockNumber="BlockNumber1">
<BlockStartTime>01:01:01.001</BlockStartTime>
<ProductionDate>2006-05-04</ProductionDate>
<BlockType>Bloc</BlockType>
<FlightOrders>
    <FlightOrder>
        <Flight>
            <FlightNr>FlightNr0</FlightNr>
            <DepartureDate>2006-05-04</DepartureDate>
            <DepartureTime>01:01:01.001</DepartureTime>
            <AircraftType>AircraftType0</AircraftType>
            <AircraftSeatConfiguration>AircraftSeatConfiguration0</AircraftSeatConfiguration>
        </Flight>
        <CATC>CATC0</CATC>
    </FlightOrder>
    <FlightOrder>
        <Flight>
            <FlightNr>FlightNr1</FlightNr>
            <DepartureDate>2006-05-04</DepartureDate>
            <DepartureTime>01:01:01.001</DepartureTime>
            <AircraftType>AircraftType1</AircraftType>
            <AircraftSeatConfiguration>AircraftSeatConfiguration1</AircraftSeatConfiguration>
        </Flight>
   </FlightOrder>
</FlightOrders>

'

SELECT @x.value('(/BlockOrderMessage/Blocktype)[1]','int') AS 'BlockType',
   @x.value('(/BlockOrderMessage/FlightOrders/FlightOrder/Flight/FlightNr)[1]','VARCHAR (20)') AS 'FlightNr',
   @x.value('(/BlockOrderMessage/FlightOrders/FlightOrder/Flight/AircraftType)[1]','VARCHAR (20)') AS 'AircraftType'   

然后我收到三列的查询结果（到目前为止一切顺利），但所有三列的记录都是 NULL。当我删除 BlockOrderMessage 中的命名空间声明时，它确实有效，因此我认为我需要在查询中的某个位置声明这些命名空间，但我找不到方法。有人我应该怎么做吗？

提前感谢！

       

Answer 1

Hy，首先，我认为它是区分大小写的，所以正确的Blocktype BlockType.不要将BlockType转换为int，因为它string因此请使用nvarchar(max)。在查询中使用 xml 命名空间，如下所示 ; WITH XMLNAMESPACES (default 'http://www.test.com/production/block')

选择 @x.value（'（/BlockOrderMessage/BlockType ）[1]'， 'varchar(max)' ） AS 'BlockType'， @x.value（'（/BlockOrderMessage/FlightOrders/FlightOrder/Flight/FlightNr）[1]'，'VARCHAR （20）'） AS 'FlightNr'， @x.value（'（/BlockOrderMessage/FlightOrders/FlightOrder/Flight/AircraftType）[1]'，'VARCHAR （20）'） AS 'AircraftType' '

这是完整的查询

DECLARE @x XML
SET @x = 
'<?xml version="1.0" encoding="UTF-8"?>
<BlockOrderMessage xmlns="http://www.test.com/production/block"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.test.com/production/block file:/C:/Users/test.xsd"
BlockNumber="BlockNumber1">
<BlockStartTime>01:01:01.001</BlockStartTime>
<ProductionDate>2006-05-04</ProductionDate>
<BlockType>Bloc</BlockType>
<FlightOrders>
    <FlightOrder>
        <Flight>
            <FlightNr>FlightNr0</FlightNr>
            <DepartureDate>2006-05-04</DepartureDate>
            <DepartureTime>01:01:01.001</DepartureTime>
            <AircraftType>AircraftType0</AircraftType>
            <AircraftSeatConfiguration>AircraftSeatConfiguration0</AircraftSeatConfiguration>
        </Flight>
        <CATC>CATC0</CATC>
    </FlightOrder>
    <FlightOrder>
        <Flight>
            <FlightNr>FlightNr1</FlightNr>
            <DepartureDate>2006-05-04</DepartureDate>
            <DepartureTime>01:01:01.001</DepartureTime>
            <AircraftType>AircraftType1</AircraftType>
            <AircraftSeatConfiguration>AircraftSeatConfiguration1</AircraftSeatConfiguration>
        </Flight>
   </FlightOrder>
</FlightOrders>
</BlockOrderMessage>'
; WITH XMLNAMESPACES (default 'http://www.test.com/production/block')
SELECT @x.value('(/BlockOrderMessage/BlockType)[1]','varchar(50)') AS 'BlockType',
   @x.value('(/BlockOrderMessage/FlightOrders/FlightOrder/Flight/FlightNr)[1]','VARCHAR (20)') AS 'FlightNr',
   @x.value('(/BlockOrderMessage/FlightOrders/FlightOrder/Flight/AircraftType)[1]','VARCHAR (20)') AS 'AircraftType'