Getting data from poorly structured xml from sql server 2014

Question

Hello I have a newbie question, apologies.

I have to work with a stupidly designed XML format that provides object attribute data as tags and where a tag value changes it implies an attribute. Where an attribute is not provided eg. mammal, it implies that the value is unknown.

Using the following as an example: 3rd level tag can be one of 20 different values. 4th level tag (mammal) can be one of several values and may not exist.

How can I write an XQuery command to get beyond an unknown/missing tag?

select t.c.value('v[1]', 'varchar(100)') as animal from @xml.nodes('/recs/rec//a1// as t.c

declare @xml xml =
'
<recs>
        <rec>
        <a>
            <a1>
                <mammal>
                    <v>dog</v>
                </mammal>
            </a1>
        </a>
    </rec>
    <rec>
        <b>
            <a1>
                <mammal>
                    <v>cat</v>
                </mammal>
            </a1>
        </b>
    </rec>
    <rec>
        <b>
            <a1>
                <v>pig</v>
            </a1>
        </b>
    </rec>
</recs>'

Thanks

Not clear what you're asking. What does "get beyond" mean? Do you mean you would like to select all fifth-level elements regardless of the name of their parent elements? /*/*/*/*/* will do that fine. — C. M. Sperberg-McQueen
– C. M. Sperberg-McQueen, Commented Oct 23, 2017 at 18:03
Yes, if the parent elements were consistently named I'd be able to write a query to get to them. My example only shows two names but there could be hundreds. — user8820770
– user8820770, Commented Oct 23, 2017 at 18:21

C. M. Sperberg-McQueen · Accepted Answer · 2017-10-23 18:27:53Z

1

To retrieve all elements at the fifth level , use

/*/*/*/*/*

In the input you show, this will retrieve two mammal elements and a pig. (You say mammal is at the fourth level; seem to be counting from zero; I'm counting from one.) If you want their children, instead, add a /*.

If you want only fifth- or sixth-level elements within a particular context, you can of course replace the asterisks with something more restrictive:

/recs/rec/b/a1/*

for the fifth level, and

/recs/rec/b/a1/*/*

for the sixth.

answered Oct 23, 2017 at 18:27

C. M. Sperberg-McQueen

25.2k6 gold badges42 silver badges65 bronze badges

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

5 Comments

user8820770 Over a year ago

How do I get the actual tag value ie. a1 or mammal? SELECT Tab.Col.value('../v[1]', 'varchar(10)') as animal ,Tab.Col.value('v[1]', 'varchar(10)') as animal FROM @xml.nodes('/recs/rec/*/a1/*') Tab(Col)

C. M. Sperberg-McQueen Over a year ago

Read a tutorial on XPath; use name() to get the element name (which is what I assume you mean by 'the actual tag value', though it's not a 'value' in XDM).

C. M. Sperberg-McQueen Over a year ago

@Shnugo - you may be right. Since the only thing the OP knows about the elements wanted is that they are at the nth level, I'm not seeing how to get them all without counting levels. And my attention is focused on what appears on the surface to be OP's question about XQuery (or, strictly speaking, XPath). If there are facilities within SQL Server's combination of SQL and XQuery that allow a better solution, fine with me.

Gottfried Lesigang Over a year ago

Yeah, you may be right too, deleted my comment... Sorry for barking :-D After reading the question once again I think, that both our answers will not fully cover the needs. voted yours up

Gottfried Lesigang Over a year ago

I just placed an update to my answer to cover the needs - hopefully

Gottfried Lesigang · Accepted Answer · 2017-10-24 06:15:39Z

No, you do not need to count the levels and combine the right count of *

Try this, it's using the deep search with //:

SELECT any_v.value('text()[1]','nvarchar(max)') AS TheTextWithinV
      ,any_v.value('local-name(..)','nvarchar(max)') AS TheNameOfTheParent
FROM @xml.nodes('//v') AS DeepSearch(any_v);

As you should be as specific as possible I'd suggest this in .nodes()

FROM @xml.nodes('/recs/rec//v') AS DeepSearch(any_v);

It will go down the path 'til <rec> then do the deep search 'til <v>.

As there is no other text() anywhere, you might even do this:

SELECT any_v.value('.','nvarchar(max)') AS FirstTextFound
FROM @xml.nodes('/recs/rec') AS DeepSearch(any_v);

UPDATE One more approach

Try this

SELECT r.value('local-name(.)','nvarchar(max)') AS Level2
      ,r.value('local-name((./*)[1])','nvarchar(max)') AS Level3
      ,r.value('if(local-name((./*/*)[1])="v") then "" else local-name((./*/*)[1])','nvarchar(max)') AS Level4
      ,r.value('(.//v/text())[1]','nvarchar(max)') AS Level5
FROM  @xml.nodes('/recs/rec/*') AS A(r);

To get this

Level2  Level3  Level4  Level5
a       a1      mammal  dog
b       a1      mammal  cat
b       a1              pig

Collectives™ on Stack Overflow

Getting data from poorly structured xml from sql server 2014

2 Answers 2

5 Comments

UPDATE One more approach

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

UPDATE One more approach

Comments

Your Answer

Sign up or log in

Post as a guest

Related