April 25, 2012 at 3:47 am
Hi All
We have a column filled with XML formatted metadata of certain resources which have been uploaded to the DB (sample below)
<MetaData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://core.company.com/MetaData.xsd">
<Title>Apr 24 2012: Some Text</Title>
<Keywords>apr 24 2012,some key words</Keywords>
<OneLineOverview>Apr 24 2012: Some Text</OneLineOverview>
<Description>Some Description.</Description>
<Discipline>
<Name>Bobbins</Name>
<Subject>ThisIsBobbins</Subject>
</Discipline>
<Discipline />
<AgeRanges>
<AgeRange>1-12</AgeRange>
</AgeRanges>
<ResourceTypes>
<ResourceType>Type1</ResourceType>
</ResourceTypes>
<FileFormat>excel</FileFormat>
<Countries>
<Country>United States</Country>
</Countries>
<Language>English</Language>
<Standard>Standard12345</Standard>
</MetaData>
There was a config issue which prevented a stored proc from running inserting the nessesary data into the DB, so I need to retrovert the data back in from the metadata, the problem I am having is just getting the <Standard> out of the data.
So far I have
MetaData.query('declare namespace NS="http://core.company.com/MetaData.xsd"; /NS:MetaData/NS:Standard')
But it returns an XML value of <NS:StateStandard xmlns:NS="http://core.company.com/MetaData.xsd">Standard12345</NS:StateStandard>, I just need it to return Standard12345 on its own, not the additional tags.
Any help would be appreciated.
Full test code
declare @xml table (metadata xml)
insert into @xml (metadata) values(
'<MetaData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://core.company.com/MetaData.xsd">
<Title>Apr 24 2012: Some Text</Title>
<Keywords>apr 24 2012,some key words</Keywords>
<OneLineOverview>Apr 24 2012: Some Text</OneLineOverview>
<Description>Some Description.</Description>
<Discipline>
<Name>Bobbins</Name>
<Subject>ThisIsBobbins</Subject>
</Discipline>
<Discipline />
<AgeRanges>
<AgeRange>1-12</AgeRange>
</AgeRanges>
<ResourceTypes>
<ResourceType>Type1</ResourceType>
</ResourceTypes>
<FileFormat>excel</FileFormat>
<Countries>
<Country>United States</Country>
</Countries>
<Language>English</Language>
<Standard>Standard12345</Standard>
</MetaData>')
select
MetaData.query('declare namespace NS="http://core.company.com/MetaData.xsd"; /NS:MetaData/NS:Standard')
from
@xml
April 25, 2012 at 3:52 am
figured it out
MetaData.value('declare namespace NS="http://core.company.com/MetaData.xsd"; (/NS:MetaData/NS:StateStandard)[1]','NVARCHAR(MAX)')
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply