TSQL Parse from XML nodes

Question

This XML comes from public treasury. SQL Server seems to think it is not XML? My result says the XLT method can only be involved with column type XML. Is this result set from the treasury really XML?

How can I parse it into a SQL table?

treasury daily market yield

DECLARE @XML NVARCHAR(MAX)
SELECT @XML = 
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<feed xml:base="http://data.treasury.gov/Feed.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
  <title type="text">DailyTreasuryYieldCurveRateData</title>
  <id>http://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData</id>
  <updated>2020-07-14T05:40:26Z</updated>
  <link rel="self" title="DailyTreasuryYieldCurveRateData" href="DailyTreasuryYieldCurveRateData"/>
  <entry>
    <id>http://data.treasury.gov/Feed.svc/DailyTreasuryYieldCurveRateData(7633)</id>
    <title type="text"/>
    <updated>2020-07-14T05:40:26Z</updated>
    <author>
      <name/>
    </author>
    <link rel="edit" title="DailyTreasuryYieldCurveRateDatum" href="DailyTreasuryYieldCurveRateData(7633)"/>
    <category term="TreasuryDataWarehouseModel.DailyTreasuryYieldCurveRateDatum" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>
    <content type="application/xml">
      <m:properties>
        <d:Id m:type="Edm.Int32">7633</d:Id>
        <d:NEW_DATE m:type="Edm.DateTime">2020-07-01T00:00:00</d:NEW_DATE>
        <d:BC_1MONTH m:type="Edm.Double">0.12</d:BC_1MONTH>
        <d:BC_2MONTH m:type="Edm.Double">0.12</d:BC_2MONTH>
        <d:BC_3MONTH m:type="Edm.Double">0.14</d:BC_3MONTH>
        <d:BC_6MONTH m:type="Edm.Double">0.17</d:BC_6MONTH>
        <d:BC_1YEAR m:type="Edm.Double">0.16</d:BC_1YEAR>
        <d:BC_2YEAR m:type="Edm.Double">0.17</d:BC_2YEAR>
        <d:BC_3YEAR m:type="Edm.Double">0.19</d:BC_3YEAR>
        <d:BC_5YEAR m:type="Edm.Double">0.31</d:BC_5YEAR>
        <d:BC_7YEAR m:type="Edm.Double">0.52</d:BC_7YEAR>
        <d:BC_10YEAR m:type="Edm.Double">0.69</d:BC_10YEAR>
        <d:BC_20YEAR m:type="Edm.Double">1.2</d:BC_20YEAR>
        <d:BC_30YEAR m:type="Edm.Double">1.43</d:BC_30YEAR>
        <d:BC_30YEARDISPLAY m:type="Edm.Double">1.43</d:BC_30YEARDISPLAY>
      </m:properties>
    </content>
  </entry>
</feed>'
   
SELECT  b.value('@d:Id', 'varchar(28)') as d_id
       ,b.value('@d:BC_30YEAR', 'double') as d_BC_30YEAR
  FROM @XML.nodes('feed/entry/content type/m:properties/') as a(b)

Change DECLARE @XML NVARCHAR(MAX) to DECLARE @XML XML

Preben Huybrechts
– Preben Huybrechts

2020-07-14 06:25:48 +00:00
Commented Jul 14, 2020 at 6:25 — Preben Huybrechts
– Preben Huybrechts, Commented Jul 14, 2020 at 6:25

marc_s · Accepted Answer · 2020-07-14 07:18:00Z

5

First, define your @XML to be of type XML:

DECLARE @XML XML;

Then: you need to define the relevant XML namespaces involved!

Try this snippet of code:

-- define XML namespaces!
;WITH XMLNAMESPACES(DEFAULT 'http://www.w3.org/2005/Atom', 
                    'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' AS m,
                    'http://schemas.microsoft.com/ado/2007/08/dataservices' AS d)
SELECT  
    b.value('(d:Id)[1]', 'varchar(28)') as d_id,
    b.value('(d:BC_30YEAR)[1]', 'decimal(20,2)') as d_BC_30YEAR
FROM 
    @XML.nodes('feed/entry/content/m:properties') as a(b)

This returns the following value:

Fixes I made:

FROM @XML.nodes('feed/entry/content type/m:properties/') as a(b)  
                            ^^^^^^^^^^^^

Wrong node name - the node is <content> (not "content type") - the type is just an attribute on the node, no relevant in this XPath here.

SELECT b.value('@d:Id', 'varchar(28)') as d_id  
                ^^^^^^

You really want to select the XML element (node) d:Id - not an attribute - the @ in @d:Id denotes an attribute!

b.value('@d:BC_30YEAR', 'double') as d_BC_30YEAR  
                        ^^^^^^^^^

You need to use a proper T-SQL datatype - like decimal(20,2) - not "double" here....

edited Jul 14, 2020 at 7:18

answered Jul 14, 2020 at 7:08

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yitzhak Khabinsky Over a year ago

Good answer, +1 from my side!

John Ligda Over a year ago

Excellent explanation. Thank you.

John Ligda Over a year ago

I do have this working fine in a stored procedure. Interestingly, it fails when schedule in a SQL Job. I've changed Agent to run as the same user I develop and and execute the SP with. Any ideas?

Collectives™ on Stack Overflow

TSQL Parse from XML nodes

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related