1

I am needing to extract a date from a nested xml and am running into the issue of no output. The date I am attempting to retrieve is for Field name="A". My expected output is 2022-04-12. Here is the sample data along with the query I have generated. Not exactly sure where I am going wrong with the query. Any help will be greatly appreciated:

declare @xml xml
set @xml = 
'<root xlmns="http://www.example.com">
    <header></header>
    <alt-header></alt-header>
    <Main>
        <Employee>
            <Department>
                <Category>
                    <Team>
                        <Name>Fun</Name>
                        <Sub>
                            <field name="A">
                                <Field-value xmlns:xsi="http://www.example2.com" xsi:type="date">
                                    <value>2022-04-12</value>
                                </Field-value>
                            </field>
                            <field name="B">
                                <Field-value xmlns:xsi="http://www.example2.com" xsi:type="date">
                                    <value>2021-04-12</value>
                                </Field-value>
                            </field>
                        </Sub>
                    </Team>
                </Category>
            </Department>
        </Employee>
        <Employee>
            <Department>
                <Category>
                    <Team>
                        <Name>Times</Name>
                        <Sub>
                            <field name="B">
                                <Field-value xmlns:xsi="http://www.cde.com" xsi:type="date">
                                    <value>2021-04-12</value>
                                </Field-value>
                            </field>
                        </Sub>
                    </Team>
                </Category>
            </Department>
        </Employee>
    </Main>
</root>';

WITH XMLNAMESPACES (DEFAULT 'http://www.example.com',
                    'http://www.example2.com' as xsi) 

SELECT  x.query('./Department/Category/Team/Name[../Name="FUN"]/Sub/Field [@name="A"]/field-value/value').value('.', 'nvarchar(100)')A_date
from @xml.nodes('/root/Main/Employee') tempxml(x)
Michael M
  • 11
  • 2

1 Answers1

0
//Department/Category/Team[Name[text()="Fun"]]/Sub/field[@name="A"]/Field-value/value

The // in the beginning means that xpath will search everythere in the document. Since you are looking within Employee elements, you can change //Department to ./Department or even Department. All three should work.

Pavel Koryakin
  • 503
  • 4
  • 10