Extract data XML columns in SQL Server

Question

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>

Dhana · Accepted Answer · 2018-10-04 21:38:40Z

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]

answered Oct 4, 2018 at 21:38

Dhana

1,6584 gold badges24 silver badges40 bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

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.

marc_s · Accepted Answer · 2018-10-05 05:01:11Z

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

answered Oct 5, 2018 at 5:01

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

3 Comments

Gottfried Lesigang Over a year ago

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.

marc_s Over a year ago

@Shnugo: thanks for the hint - I'll try to keep that in mind for the future

Gottfried Lesigang Over a year ago

I posted an answer with some details about text(). Might be interesting..

Collectives™ on Stack Overflow

Extract data XML columns in SQL Server

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related