CODEWITHSUNDEEP
sqlsql-serverxml
Exanimis
Exanimis

Reputation: 23

Flattening xml data in sql

I'm trying to flatten XML data in a SQL query but I always seem to get nulls.

I tried the cross/outer apply method described here.

The column with XML data is called Data.

I'm guessing that the xml data with these links need to be somehow also added?

Could you please help to get a proper SQL query?

Query I tried:

SELECT
 v.name
 ,pref.value('(LocalId/text())[1]', 'nvarchar(10)') as localid
FROM [database].[requests] v
outer apply v.Data.nodes('/DataForm') x(pref)
GO

example of xml data in that column:

  <Dataform xmlns="http://somelongasslink.org/hasalsosomestuffhere" xmlns:i="http://somexlmschemalink/">
    <DeleteDate xmlns="http://somelongasslink.org/hasalsosomestuffhere" i:nil="true" />
    <LocalId xmlns="http://somelongasslink.org/hasalsosomestuffhere">5325325</LocalId>
  ...

Upvotes: 2

Views: 769

Answers (1)

marc_s
marc_s

Reputation: 754538

You can use this code to get the result you're looking for:

;WITH XMLNAMESPACES(DEFAULT 'http://somelongasslink.org/hasalsosomestuffhere')
SELECT
    rq.Name,
    LocalID = TC.value('(LocalId)[1]', 'nvarchar(10)') 
FROM 
     [database].[requests] rq
CROSS APPLY
    rq.Data.nodes('/Dataform') AS TX(TC)
GO

There were two problems with your code:

  1. you're not respecting / including the XML namespace that's defined on the XML document

    <Dataform xmlns="http://somelongasslink.org/hasalsosomestuffhere" 
          *******************************************************

  2. you didn't pay attention to the case-sensitivity of XML in your call to .nodes() - you need to use .nodes('/Dataform') (not /DataForm - the F is not capitalized in your XML)

Upvotes: 2

Related Questions