Parse XML value with Name Space in SQl

Question

I have following XML :

<ProductionSchedule xmlns:inp2="http://www.wbf.org/xml/B2MML-V0401" xmlns="http://www.wbf.org/xml/B2MML-V0401">
  <inp2:ProductionRequest>
    <inp2:ID>0916A</inp2:ID>
    <inp2:Description>SUBH190916A</inp2:Description>
    <inp2:Location>
      <inp2:EquipmentID>MYEqupiment</inp2:EquipmentID>
    </inp2:Location>
    <inp2:SegmentRequirement>
      <inp2:ID>000</inp2:ID>
      <inp2:EarliestStartTime>2015-10-17T12:00:00</inp2:EarliestStartTime>
      <inp2:LatestEndTime>2015-10-19T12:00:00</inp2:LatestEndTime>
      <inp2:MaterialProducedRequirement>
        <inp2:MaterialDefinitionID>GEEC3MA0025EMZI</inp2:MaterialDefinitionID>
        <inp2:Quantity>
          <inp2:QuantityString>2</inp2:QuantityString>
        </inp2:Quantity>
        <inp2:MaterialProducedRequirementProperty>
          <inp2:ID>ERPWOStatus</inp2:ID>
          <inp2:Value>
            <inp2:ValueString>Released</inp2:ValueString>
          </inp2:Value>
        </inp2:MaterialProducedRequirementProperty>
        <inp2:MaterialProducedRequirementProperty>
          <inp2:ID>ROUTING</inp2:ID>
          <inp2:Value>
            <inp2:ValueString>SOmeMPRVaue</inp2:ValueString>
          </inp2:Value>
        </inp2:MaterialProducedRequirementProperty>
        <inp2:MaterialProducedRequirementProperty>
          <inp2:ID>MPValue2</inp2:ID>
          <inp2:Value>
            <inp2:ValueString>2016-01-21T12:00:00</inp2:ValueString>
          </inp2:Value>
        </inp2:MaterialProducedRequirementProperty>
      </inp2:MaterialProducedRequirement>
    </inp2:SegmentRequirement>
  </inp2:ProductionRequest>
</ProductionSchedule>

I am trying to get the value MPValue2 , from the XML.

I tried with following:

Select `@xml.value('(/ProductionSchedule/inp2:ProductionRequest/inp2:SegmentRequirement/inp2:MaterialProducedRequirement/inp2:MaterialProducedRequirementProperty)[1]','nvarchar(255)')`

Gottfried Lesigang · Accepted Answer · 2016-09-22 14:22:12Z

1

Your select is OK, but you must consider/declare the namespaces:

WITH XMLNAMESPACES(DEFAULT 'http://www.wbf.org/xml/B2MML-V0401'
                  ,'http://www.wbf.org/xml/B2MML-V0401' AS inp2)
Select @xml.value('(/ProductionSchedule/inp2:ProductionRequest/inp2:SegmentRequirement/inp2:MaterialProducedRequirement/inp2:MaterialProducedRequirementProperty)[1]','nvarchar(255)')

This works too (wildcard) but it's better to be as specific as possible:

Select @xml.value('(/*:ProductionSchedule/*:ProductionRequest/*:SegmentRequirement/*:MaterialProducedRequirement/*:MaterialProducedRequirementProperty)[1]','nvarchar(255)')

The fast and lazy would work too :-) but not fast in terms of performance...

Select @xml.value('(//*:MaterialProducedRequirementProperty)[1]','nvarchar(255)')

UPDATE

This is the query to get all your Properties:

WITH XMLNAMESPACES(DEFAULT 'http://www.wbf.org/xml/B2MML-V0401'
                  ,'http://www.wbf.org/xml/B2MML-V0401' AS inp2)
SELECT prop.value('(inp2:ID)[1]','nvarchar(100)') AS Property
FROM @xml.nodes('/ProductionSchedule/inp2:ProductionRequest/inp2:SegmentRequirement/inp2:MaterialProducedRequirement/inp2:MaterialProducedRequirementProperty') AS A(prop)

The result

Property
--------
ERPWOStatus
ROUTING
MPValue2

UPDATE 2: Use the ID as filter in `XQuery`

See how I added the filter at the end of the XPath in .nodes().

Nodes will return all sub-elements row-wise. The filter will reduce the resultset to one single row (if inp2:ID is unique!) and then read the Value/ValueString.

I let the namespace declaration for DEFAULT and inp2. But, as @Serf pointed out correctly, both URLs are equal. It would be enough to declare only the DEFAULT and query without any namespace-prefixes...

DECLARE @TheID NVARCHAR(100)='MPValue2';
WITH XMLNAMESPACES(DEFAULT 'http://www.wbf.org/xml/B2MML-V0401'
                  ,'http://www.wbf.org/xml/B2MML-V0401' AS inp2)
SELECT prop.value('(inp2:Value/inp2:ValueString)[1]','nvarchar(100)') AS Property
FROM @xml.nodes('/ProductionSchedule/inp2:ProductionRequest/inp2:SegmentRequirement/inp2:MaterialProducedRequirement/inp2:MaterialProducedRequirementProperty[inp2:ID=sql:variable("@TheID")]') AS A(prop)

edited Sep 22, 2016 at 14:22

answered Sep 22, 2016 at 12:43

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

9 Comments

Simsons Over a year ago

But I need to get the value for 'SOmeMPRVaue' which is on third MaterialProducedRequirementProperty. And I do not want to go by Index , but whose node value is SOmeMPRVaue , get the ValueString

Gottfried Lesigang Over a year ago

@Simsons This should work with the path prop.value('(inp2:Value/inp2:ValueString)[1]','nvarchar(100)

Simsons Over a year ago

Can we use where clause instead of index?

Gottfried Lesigang Over a year ago

@Simsons, you might read the data as derived table and use a WHERE, or you can add a filter condition (XQuery) into the XPath. What do you want to filter? What is the unique name/id/mark to know which node to read?

Serg Over a year ago

By chance both namespaces are the equal, so DEFAULT declaration and unprefixed names '/ProductionSchedule/ProductionRequest...' should be quite enough,

|

Collectives™ on Stack Overflow

Parse XML value with Name Space in SQl

1 Answer 1

UPDATE

UPDATE 2: Use the ID as filter in `XQuery`

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

UPDATE 2: Use the ID as filter in XQuery

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related

UPDATE 2: Use the ID as filter in `XQuery`