Can't parse XML with outer apply

Question

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 How to parse XML data in SQL server table or Query XML with nested nodes on Cross Apply.

It doesn't work.

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)

As already mentioned by @marc_s, the XML is not well-formed. — Yitzhak Khabinsky, Oct 24 '21 at 21:06
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Oct 24 '21 at 21:06
@YitzhakKhabinsky thanks for info about what i should include in posts. I figured the XML text and the sql code i am running was enough but i understand some people could be on really old versions of sql server so i will think that in future. — Mattias W, Oct 25 '21 at 08:14
Now that i understand namespace thing was the issue i searched a bit and found for example https://stackoverflow.com/questions/22818591/sql-server-xml-query-with-multiple-namespaces/27449609 and comment vittore which seem to imply that not needed to use WITH and name space declaration. Instead can just accept any namespace. However i didn't get that to work yet but interesting as next step. I have incentive to try and work without CTE. — Mattias W, Oct 25 '21 at 08:16

marc_s · Answer 1 · 2021-10-25T12:35:54.810

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.....

UPDATE:

If you need to also access bits in the Payload section - you need to also respect that XML namespace there:

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

Try this:

WITH XMLNAMESPACES(N'http://iec.ch/TC57/2011/schema/message' as hdr,
                   N'http://iec.ch/TC57/2011/MeterReadings#' as mr)
SELECT
    t.id,
    c.value('(hdr:Timestamp)[1]', 'varchar(50)') AS timestamp,
    col.value('(mr:MeterReading/mr:IntervalBlocks/mr:IntervalReadings/mr:timeStamp)[1]', 'varchar(50)') AS MeterReadingsTimestamp
FROM
    load.t t
CROSS APPLY
    t.xml_data.nodes('/hdr:RequestMessage/hdr:Header') AS m(c)
CROSS APPLY
    t.xml_data.nodes('/hdr:RequestMessage/hdr:Payload/mr:MeterReadings') AS mr(col)

How come it works for Header but when i run it (with changing the namespace) for Payload it doesn't work? `WITH XMLNAMESPACES(DEFAULT N'http://iec.ch/TC57/2011/MeterReadings#')` `SELECT t.file_name,c.value('(/UsagePoint/mRID)[1]','varchar(max)') as timestamp` `FROM load.t t ` `OUTER APPLY t.xml_data.nodes('RequestMessage/Payload') AS m(c)` Yitzak mentions there are 2 namespaces for Payload. Could that be the issue? Do need to load both namespaces to the WITH cte somehow? — Mattias W, Oct 25 '21 at 12:26
@MattiasW: again - you have a XML namespace and you're not integrating that in your XQuery! Updated my response with a sample of how to do this. — marc_s, Oct 25 '21 at 12:38
Now i see how you are using Namespace. It seems to bind to the alias so it kind of binds "hdr:" to 'http://iec.ch/TC57/2011/schema/message' and "mr:" to http://iec.ch/TC57/2011/MeterReadings#. — Mattias W, Oct 25 '21 at 14:26
@MattiasW: yes - exactly - the namespace is really the "http://iec.ch/TC57/2011/MeterReadings#" part - the *prefix* of it is used to refer to it in your XPath expressions — marc_s, Oct 25 '21 at 15:44

score 1 · Accepted Answer · answered Oct 24 '21 at 21:24

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 |
+----+---------------+----------------------------+

Can't parse XML with outer apply

2 Answers2