Extract data from xml column in SQL Server

Question

I have an XML column in SQL Server populated with:

<concession>
  <schema>
    <data schemaItem="title">Re-label to all boards</data>
    <data schemaItem="problem">These boards have been tested</data>
    <data schemaItem="solution">Ask to print new label and add the word "B" on the old serial numbers. so all serial numbers will be modified to new on B .......</data>
    <data schemaItem="justification">Will help UK test resource</data>
    <data schemaItem="liability">Us</data>
    <data schemaItem="parts">
      <part>075</part>
      <part>076</part>
    </data>
    <data schemaItem="products">
      <product>Pdq </product>
    </data>
    <data schemaItem="faultCode">ILB</data>
    <data schemaItem="processCode">MAT</data>
    <data schemaItem="quantity">273</data>
    <data schemaItem="requestedExpiry">14/12/2011</data>
  </schema>
</concession>

How do I extract the quantity value, ie: 273?

I've tried but no joy:

SELECT 
    [guid],XMLData, 
    (select xmlData.value('(/concession/schema/data)[1]', 'varchar(100)' )), 
    (select xmlData.value('(/concession/schema/data[schemaItem="quantity"])[0]', 'varchar(100)' ))   
FROM 
    tc_Concession

I get the title ok but not the quantity.

Mahesh · Accepted Answer · 2015-03-25 17:21:27Z

1

Try using the Outer Apply With the nodes() method,

SELECT
      m.c.value('@schemaItem', 'varchar(max)') as SchemaItem,
      m.c.value('(text())[1]', 'nvarchar(max)') as Value
FROM Yourtablename
    OUTER APPLY xml_data.nodes('concession/schema/data') as m(c)

This will give you the output as,

SchemaItem         Value
title            Re-label to all boards
problem          These boards have been tested
solution         Ask to print new label and add the word "B" on the old serial numbers. so all serial numbers will be modified to new on B .......
justification    Will help UK test resource
liability        Us
parts            NULL
products         NULL
faultCode        ILB
processCode      MAT
quantity         273
requestedExpiry  14/12/2011

answered Mar 25, 2015 at 17:21

Mahesh

8,9422 gold badges37 silver badges55 bronze badges

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

Comments

Rhys Jones · Accepted Answer · 2015-03-25 17:26:13Z

1

You are very close. The attribute name needs to be preceeded by @, and the index should be 1-based. You don't need the sub-select - the .value() method can be used on it's own.

SELECT 
    [guid],
    XMLData, 
    xmlData.value('(/concession/schema/data)[1]', 'varchar(100)' ),
    xmlData.value('(/concession/schema/data[@schemaItem="quantity"])[1]', 'varchar(100)' )
FROM 
    tc_Concession

If the XML isn't guaranteed to be ordered then you should use the 2nd approach for the title too.

answered Mar 25, 2015 at 17:26

Rhys Jones

5,5361 gold badge25 silver badges44 bronze badges

Collectives™ on Stack Overflow

Extract data from xml column in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related