Can't parse XML with outer apply-CodePudding

I have an XML column in a table which i am trying to parse out values from to flat table structure.

I am trying to input the XML here but stackoverflow ses it as code and when i try and format as code it still won't accept it.

I can't even get data from "Header" level.

<RequestMessage xmlns="http://iec.ch/TC57/2011/schema/message" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Message.xsd">
  <Header>
    <Verb>created</Verb>
    <Noun>MeterReadings</Noun>
    <Timestamp>2021-03-08T00:57:18 01:00</Timestamp>
    <Source>Ipsum Lorum</Source>
    <AsyncReplyFlag>true</AsyncReplyFlag>
    <AckRequired>true</AckRequired>
    <MessageID>Ipsum Lorum</MessageID>
    <CorrelationID />
  </Header>
  <Payload>
    <MeterReadings xmlns:MeterReadings="http://iec.ch/TC57/2011/MeterReadings#" xmlns="http://iec.ch/TC57/2011/MeterReadings#">
      <MeterReading>
        <IntervalBlocks>
          <IntervalReadings>
            <timeStamp>2021-03-07T01:00:00 01:00</timeStamp>
            <value>480.196</value>
            <ReadingQualities>
              <ReadingQualityType ref="3.0.0" />
            </ReadingQualities>
          </IntervalReadings>
          <IntervalReadings>
            <ReadingType ref="11.0.7.3.1.2.12.1.1.0.0.0.0.101.0.3.72.0" />
          </IntervalReadings>
        </IntervalBlocks>
        <Meter>
          <mRID>0000000000000</mRID>
          <status>
            <remark>Ipsum Lorum</remark>
            <value>ESP</value>
          </status>
        </Meter>
        <UsagePoint>
          <mRID>73599900000000</mRID>
        </UsagePoint>
      </MeterReading>
    </MeterReadings>
  </Payload>
</RequestMessage>

I am not able to parse it and i have tried using examples from other threads. I am trying to not use OPENXML solution because requires DECLARE and executing the built in procedure for clearing the XML from memmory periodically. I am trying to use the OUTER APPLY solution. Like Shugos solution in It returns null for the timestamp column.

select 
t.file_created_time
,c.value('(Timestamp)[1]','varchar(max)') as timestamp
from load.t t
OUTER APPLY t.xml_data.nodes('RequestMessage/Header') as m(c)

CodePudding user response：

You need to respect and include the XML namespace in your XML document in your XQuery!

<RequestMessage xmlns="http://iec.ch/TC57/2011/schema/message"
                **********************************************

Try something like this:

WITH XMLNAMESPACES(DEFAULT N'http://iec.ch/TC57/2011/schema/message')
SELECT
    t.id,
    c.value('(Timestamp)[1]','varchar(max)') as timestamp
FROM
    load.t t
CROSS APPLY
    t.xml_data.nodes('RequestMessage/Header') AS m(c)

Also when trying to run this on my SQL Server, I get an error that the XML as shown is malformed.....

CodePudding user response：

Please try the following solution.

Starting from SQL Server 2005 onwards, it is better to use XQuery language, based on the w3c standards, while dealing with the XML data type.

Microsoft proprietary OPENXML and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with the obsolete SQL Server 2000. Their use is diminished just to very few fringe cases.

I had to comment out the following tag  to make your XML well-formed.

XML Header fragment has a default namespace:

xmlns="http://iec.ch/TC57/2011/schema/message"

XML Payload fragment has its own two additional namespaces:

xmlns:MeterReadings="http://iec.ch/TC57/2011/MeterReadings#"
xmlns="http://iec.ch/TC57/2011/MeterReadings#"

Namespaces should be taken into account.

Check it out below.

SQL

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xml_data XML);
INSERT INTO @tbl (xml_data) VALUES
(N'<RequestMessage xmlns="http://iec.ch/TC57/2011/schema/message"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xsi:noNamespaceSchemaLocation="Message.xsd">
    <Header>
        <Verb>created</Verb>
        <Noun>MeterReadings</Noun>
        <Timestamp>2021-03-08T00:57:18 01:00</Timestamp>
        <Source>Ipsum Lorum</Source>
        <AsyncReplyFlag>true</AsyncReplyFlag>
        <AckRequired>true</AckRequired>
        <MessageID>Ipsum Lorum</MessageID>
        <CorrelationID/>
    </Header>
    <Payload>
        <MeterReadings xmlns:MeterReadings="http://iec.ch/TC57/2011/MeterReadings#"
                       xmlns="http://iec.ch/TC57/2011/MeterReadings#">
            <MeterReading>
                <IntervalBlocks>
                    <IntervalReadings>
                        <timeStamp>2021-03-07T01:00:00 01:00</timeStamp>
                        <value>480.196</value>
                        <ReadingQualities>
                            <ReadingQualityType ref="3.0.0"/>
                        </ReadingQualities>
                    </IntervalReadings>
                    <!--<IntervalReadings>-->
                    <ReadingType ref="11.0.7.3.1.2.12.1.1.0.0.0.0.101.0.3.72.0"/>
                </IntervalBlocks>
                <Meter>
                    <mRID>0000000000000</mRID>
                    <status>
                        <remark>Ipsum Lorum</remark>
                        <value>ESP</value>
                    </status>
                </Meter>
                <UsagePoint>
                    <mRID>73599900000000</mRID>
                </UsagePoint>
            </MeterReading>
        </MeterReadings>
    </Payload>
</RequestMessage>');
-- DDL and sample data population, end

WITH XMLNAMESPACES(DEFAULT 'http://iec.ch/TC57/2011/schema/message')
SELECT id
    , c.value('(Noun/text())[1]','VARCHAR(30)') AS Noun
    , c.value('(Timestamp/text())[1]','DATETIMEOFFSET(0)') AS [timestamp]
FROM @tbl
    CROSS APPLY xml_data.nodes('/RequestMessage/Header') AS t(c);

Output

 ---- --------------- ---------------------------- 
| id |     Noun      |         timestamp          |
 ---- --------------- ---------------------------- 
|  1 | MeterReadings | 2021-03-08 00:57:18  01:00 |
 ---- --------------- ----------------------------