0

I am not getting any results so what am I doing wrong?

I have tried the examples that are given in this forum and in tech documentation.

This is the data in a table called OrderStatusResponse_2019 in a column called Response_XML with a datatype of XML. I am trying to get the resulttype back for this xml with this code.

DECLARE @Table TABLE (Response_XML XML)

INSERT INTO @Table (Response_XML) 
VALUES (<orderStatusSummaryReply xmlns="http://www.airversent.com/integration" transactionId="1" timestamp="2019-01-21T21:13:19.144Z">
  <result>
    <resultType>success</resultType>
  </result>
</orderStatusSummaryReply>)

SELECT
    XC.value('(resultType)[1]', 'NVARCHAR(10)') AS [ResultType]
FROM
    OrderStatusResponse_2019
CROSS APPLY
    Response_XML.nodes('/orderStatusSummaryReply/result') as T2(XC)

I am getting 0 rows returned. Why?

I should get back one row with Success in it. I'm getting 0 rows.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3058911
  • 1

1 Answers1

2

Your XML has a default namespace. You need to specify it, otherwise the query will not work.

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, Response_XML XML);

INSERT INTO @tbl (Response_XML) 
VALUES (N'<orderStatusSummaryReply xmlns="http://www.airversent.com/integration" transactionId="1" timestamp="2019-01-21T21:13:19.144Z">
  <result>
    <resultType>success</resultType>
  </result>
</orderStatusSummaryReply>');
-- DDL and sample data population, end

;WITH XMLNAMESPACES(DEFAULT 'http://www.airversent.com/integration')
SELECT 
    ID,
    c.value('(resultType/text())[1]', 'VARCHAR(10)') AS [ResultType]
FROM 
    @tbl
CROSS APPLY 
    Response_XML.nodes('/orderStatusSummaryReply/result') AS t(c);

Output

+----+------------+
| ID | ResultType |
+----+------------+
|  1 | success    |
+----+------------+
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21
  • Thanks. I didn't know about the syntax of the xmlnamespaces to put the word DEFAULT in the front. I had tried it without that. I hadn't seen that before. Also, why do you need the /text() after resultType? I have not seen that in syntax before either? I guess it works without it. – user3058911 Sep 20 '19 at 15:42
  • XML is inherently hierarchical. The /text() is picking up right away a textual value after the resultType element. You can create both SELECT statements with and without text() in the same SSMS Query window and turn on the Execution Plan for comparison. You will see how performant is SELECT with the text(). – Yitzhak Khabinsky Sep 20 '19 at 16:39
  • @user3058911 If interested: [Some details upon `/text()`](https://stackoverflow.com/a/43242238/5089204) and why this is faster. – Shnugo Nov 02 '19 at 10:32