CODEWITHSUNDEEP
sqlsql-serverxmlxpathxquery
omicronlyrae
omicronlyrae

Reputation: 255

SQL - return xpath for multiple XML results

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?

Upvotes: 1

Views: 66

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

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

Upvotes: 1

D-Shih
D-Shih

Reputation: 46249

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

Upvotes: 1

Related Questions