SQL - return xpath for multiple XML results

Question

I have this query:

select XMLMetadata from taObjectMetadata where ObjectMetadataTypeId = 1

which returns 3000+ rows, each row containing XML:

<objectMetaData>
  <fileLocation fileName="CM63951.mxf06092018233409;21.png" />
</objectMetaData>

I need to pull that fileName out. I can do this for any given row easily enough with XQuery:

declare @x XML = ('<objectMetaData>
  <fileLocation fileName="CM63951.mxf06092018233409;21.png" />
</objectMetaData>')

select x.value(N'@fileName', N'nvarchar(100)') as Filename
from @x.nodes(N'/objectMetaData/fileLocation') t(x)

Which gives me exactly the bit I need. However, I need this for every set of this XML in the table. Attempting to put the query in the declaration XML fails because it of course returns multiple results.

Do I need to use a WHILE loop here. or is there a better/more elegant way to do it?

D-Shih · Accepted Answer · 2018-12-13 00:30:49Z

1

You can try to use CROSS APPLY

select x.value('@fileName', N'nvarchar(100)')  as Filename
FROM yourTable CROSS APPLY yourTable.data.nodes(N'objectMetaData/fileLocation') as t(x)

NOTE : yourTable could instead your current result set.

Your query might be like this.

select x.value('@fileName', N'nvarchar(100)')  as Filename
FROM (
  select XMLMetadata 
  from taObjectMetadata 
  where ObjectMetadataTypeId = 1
)  t1 CROSS APPLY t1.XMLMetadata.nodes(N'objectMetaData/fileLocation') as t(x)

Here is a sample : sqlfiddle

edited Dec 13, 2018 at 0:30

answered Dec 13, 2018 at 0:19

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

Comments

Gottfried Lesigang · Accepted Answer · 2018-12-13 07:23:44Z

1

Your question does not make clear, whether the XML is always like the one you show (just one <fileLocation>) or if this structure might contain more.

If it's just one the question might be: Why XML?

Anyhow: This is a mockup-scenario for both cases:

DECLARE @mockup TABLE(ID INT IDENTITY,TheXml XML);

INSERT INTO @mockup VALUES
    ('<objectMetaData>
    <fileLocation fileName="CM63951.mxf06092018233409;21.png" />
    </objectMetaData>')
,('<objectMetaData>
    <fileLocation fileName="OneMore.png" />
    </objectMetaData>')
,('<objectMetaData>
    <fileLocation fileName="TheFirst.png" />
    <fileLocation fileName="TheSecond.png" />
    </objectMetaData>');

You can pick the first directly. No derived table needed

--Returns the only (or the first) file name
SELECT m.ID 
        ,TheXml.value('(/objectMetaData/fileLocation/@fileName)[1]','nvarchar(max)')
FROM @mockup m

--If there are many <fileLocation>-nodes, we need the derived table via .nodes()

--Returns multiple filenames
SELECT m.ID 
        ,fl.value('@fileName','nvarchar(max)')
FROM @mockup m
CROSS APPLY TheXml.nodes('/objectMetaData/fileLocation') A(fl);

So for your actual query this should be either this

--for one filename per row
select XMLMetadata.value('(/objectMetaData/fileLocation/@fileName)[1]','nvarchar(max)')
from taObjectMetadata 
where ObjectMetadataTypeId = 1

or this

--for many filenames per row
select fl.value('@fileName','nvarchar(max)')
from taObjectMetadata 
cross apply XMLMetadata.nodes('/objectMetaData/fileLocation') A(fl)
where ObjectMetadataTypeId = 1

answered Dec 13, 2018 at 7:23

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

1 Comment

omicronlyrae Over a year ago

It's 'just one' and as to 'Why XML' it's not my code, and I asked myself that same question - the data presented could easily have just been data...! Nevertheless, this si what I have to work with... I'll try these, thanks!

Collectives™ on Stack Overflow

SQL - return xpath for multiple XML results

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related