0

I have a table with 70K records with a column XMLMetadata - that column holds all the 70k xml data.

I need a way to extra a item from the xml columns for all 70K records. The item name that I need to pull from all 70k is <Item Name="DocTitle" Type="String">.

Is there a way I can easily pull this?

<Metadata>
    <Item Name="ID" Type="String">1364416</Item>
    <Item Name="Name" Type="String">website</Item>
    <Item Name="Type" Type="String">WebContent</Item>
    <Item Name="Title" Type="String">Close Out Letter 11/1/17</Item>
    <Item Name="Author" Type="String">Seba</Item>
    ....
</Metadata>
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
thaibythai
  • 41
  • 1
  • 4

2 Answers2

1

Try this query 

SELECT  
      XMLMetadata.value('(/Metadata/node())[1]', 'nvarchar(max)') as ID, 
      XMLMetadata.value('(/Metadata/node())[2]', 'nvarchar(max)') as Name, 
      XMLMetadata.value('(/Metadata/node())[3]', 'nvarchar(max)') as Type, 
      XMLMetadata.value('(/Metadata/node())[4]', 'nvarchar(max)') as Title, 
      XMLMetadata.value('(/Metadata/node())[5]', 'nvarchar(max)') as Author 
FROM [myTable]
Dhana
  • 1,618
  • 4
  • 23
  • 39
  • Well, the element order is an inherent part of the document, so you can rely on it. But we don't know, if this XML is always constructed in this way. What if some values are `NULL` and the element is missing? @marc_s answer is providing an approach to pick the element through its name or to return a full EAV-list. – Shnugo Oct 05 '18 at 08:53
1

If you want to get all items with the name, type and value, you could use something like this:

SELECT
    ItemName = XC.value('(@Name)', 'varchar(20)'),
    ItemType = XC.value('(@Type)', 'varchar(20)'),
    ItemValue = XC.value('(.)', 'varchar(50)')
FROM
    dbo.YourTableNameHere
CROSS APPLY 
    XmlMetadata.nodes('/Metadata/Item') AS XT(XC)

and if you want to get just a single value, based on the Name attribute, you could use this code here:

SELECT
    ItemValue = XmlMetadata.value('(/Metadata/Item[@Name="Title"]/text())[1]', 'varchar(50)')
FROM
    dbo.YourTableNameHere
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    Upvote from my side and a tiny hint: I'd suggest to use `text()[1]` instead of `(.)`. In a case as simple as above this will not make any differences, but in a more complex scenario (with nested nodes) this might lead to unexpected results. – Shnugo Oct 05 '18 at 08:51
  • @Shnugo: thanks for the hint - I'll try to keep that in mind for the future – marc_s Oct 05 '18 at 09:20
  • I posted [an answer](https://stackoverflow.com/a/43242238/5089204) with some details about `text()`. Might be interesting.. – Shnugo Oct 05 '18 at 09:27