CODEWITHSUNDEEP
sql-serverxmlattributesxquerysql-server-2017
Yogev Latin
Yogev Latin

Reputation: 31

Select dates list from xml field in SQL server

I am trying to select the dates from the following XML:

<output xmlns="http://www.abcde.com/pqwlv/dwh" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="46177" xsi:schemaLocation="dwhOutput.xsd">
  <parameter>
    <isCoupon>0</isCoupon>
  </parameter>
  <schedule>
    <executionDate businessDay="2020-01-09">2020-01-09</executionDate>
    <endDates>
      <item businessDay="2019-03-18">2019-03-17</item>
      <item businessDay="2019-06-17">2019-06-17</item>
      <item businessDay="2019-09-17">2019-09-17</item>
      <item businessDay="2019-12-17">2019-12-17</item>
      <item businessDay="2020-03-17">2020-03-17</item>
      <item businessDay="2020-06-17">2020-06-17</item>
    </endDates>
  </schedule>
</output>

And I want to select the dates list ("businessDay") from "endDates" field, So the output will be (as a table):

endDates
----------
2019-03-18
2019-06-17
2019-09-17
2019-12-17
2020-03-17
2020-06-17

I have tried to use this query:

SELECT endDates.query('.') AS endDates
FROM [table]
CROSS APPLY
OUTPUT.nodes('declare namespace ns="http://www.abcde.com/pqwlv/dwh"; /ns:output/ns:schedule/ns:endDates') AS T1(endDates)

And got:

<p1:endDates xmlns:p1="http://www.abcde.com/pqwlv/dwh">
  <p1:item businessDay="2019-03-18">2019-03-17</p1:item>
  <p1:item businessDay="2019-06-17">2019-06-17</p1:item>
  <p1:item businessDay="2019-09-17">2019-09-17</p1:item>
  <p1:item businessDay="2019-12-17">2019-12-17</p1:item>
  <p1:item businessDay="2020-03-17">2020-03-17</p1:item>
  <p1:item businessDay="2020-06-17">2020-06-17</p1:item>
</p1:endDates>

How can I dig deeper and select the dates?

Upvotes: 3

Views: 44

Answers (3)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22157

It seems here is the fastest and clean method. No need to declare the xsi namespace because it is not used.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, xml_data XML);
INSERT INTO @tbl
VALUES (N'<output xmlns="http://www.abcde.com/pqwlv/dwh" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        id="46177" xsi:schemaLocation="dwhOutput.xsd">
    <parameter>
        <isCoupon>0</isCoupon>
    </parameter>
    <schedule>
        <executionDate businessDay="2020-01-09">2020-01-09</executionDate>
        <endDates>
            <item businessDay="2019-03-18">2019-03-17</item>
            <item businessDay="2019-06-17">2019-06-17</item>
            <item businessDay="2019-09-17">2019-09-17</item>
            <item businessDay="2019-12-17">2019-12-17</item>
            <item businessDay="2020-03-17">2020-03-17</item>
            <item businessDay="2020-06-17">2020-06-17</item>
        </endDates>
    </schedule>
</output>');
-- DDL and sample data population, end

;WITH XMLNAMESPACES(DEFAULT 'http://www.abcde.com/pqwlv/dwh')
SELECT ID
    , col.value('.','DATE') AS endDates
FROM @tbl AS tbl
    CROSS APPLY tbl.xml_data.nodes('/output/schedule/endDates/item/@businessDay') AS tab(col);

Output

+----+------------+
| ID |  endDates  |
+----+------------+
|  1 | 2019-03-18 |
|  1 | 2019-06-17 |
|  1 | 2019-09-17 |
|  1 | 2019-12-17 |
|  1 | 2020-03-17 |
|  1 | 2020-06-17 |
+----+------------+

Upvotes: 0

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

You are close to your desired output.

SELECT  T1.endDates.value('@businessDay','DATE')
FROM(
SELECT @X AS COL
)A 
CROSS APPLY
A.COL.nodes('declare namespace ns="http://www.abcde.com/pqwlv/dwh"; 
/ns:output/ns:schedule/ns:endDates/ns:item') AS T1(endDates)

Output:

(No column name)
2019-03-18
2019-06-17
2019-09-17
2019-12-17
2020-03-17
2020-06-17

Upvotes: 0

Thom A
Thom A

Reputation: 95544

You weren't far off. You need to use WITH XMLNAMESPACES at the start of your query, to define the namespace, and then you can transverse the nodes in the FROM:

DECLARE @xml xml = '<output xmlns="http://www.abcde.com/pqwlv/dwh" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="46177" xsi:schemaLocation="dwhOutput.xsd">
  <parameter>
    <isCoupon>0</isCoupon>
  </parameter>
  <schedule>
    <executionDate businessDay="2020-01-09">2020-01-09</executionDate>
    <endDates>
      <item businessDay="2019-03-18">2019-03-17</item>
      <item businessDay="2019-06-17">2019-06-17</item>
      <item businessDay="2019-09-17">2019-09-17</item>
      <item businessDay="2019-12-17">2019-12-17</item>
      <item businessDay="2020-03-17">2020-03-17</item>
      <item businessDay="2020-06-17">2020-06-17</item>
    </endDates>
  </schedule>
</output>';

WITH XMLNAMESPACES(DEFAULT 'http://www.abcde.com/pqwlv/dwh',
                   'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT eD.item.value('@businessDay','date') AS endDate
FROM (VALUES(@XML))V(YourXML) --This would be your table
     CROSS APPLY V.YourXML.nodes('/output/schedule/endDates/item') eD(item);

Upvotes: 1

Related Questions