CODEWITHSUNDEEP
sql-serverxmlt-sql
Question3CPO
Question3CPO

Reputation: 1202

TSQL: Extract XML Nested Tags Into Columns

Using SQLServer2008R2

I currently have XML tags with data inside the XML tags (not between them), such as:

<zooid="1"><animals key="all" zebras="22" dogs="0" birds="4" /><animals key="all" workers="yes" vacation="occasion" /> ... *(more)*</zooid>
<zooid="2"><animals key="house" zebras="0" dogs="1" birds="2" /><animals key="house" workers="no" vacation="no" /> ... *(more)*</zoodid>

If I query the XML or use the value function against it, it returns blank values because it tries to read between tags - where no value exists. I need it to read inside of the tags, parse out the values before the equal sign as columns and the values between the quotations as values inside those columns (granted, I could create a function that could do this, but this would be quite meticulous, and I'm curious if something like this already exists). What it should look like this in columns:

Key | Zebras | Dogs | Birds | Key | Workers | Vacation | ... *(more)*

... and this in rows of data:

all   | 22 | 0 | 4 | all   | yes | occasion | ... *(more)*
house | 0  | 1 | 2 | house | no  | no       | ... *(more)*

So the final output (just using the two XML rows from the beginning for now), would look like the below data in table form:

Key   | Zebras | Dogs | Birds | Key    | Workers | Vacation | ... *(more)*
================================================================
all   | 22     | 0    | 4     | all   | yes     | occasion | ... *(more)*
house | 0      | 1    | 2     | house | no      | no       | ... *(more)*

Other than querying against XML, using the .query tool and even trying the .node tool (using CROSS APPLY see this thread), I haven't been able to generate this.

Upvotes: 0

Views: 2299

Answers (2)

Devart
Devart

Reputation: 122032

Try this one -

DECLARE @YourXML NVARCHAR(MAX)
SELECT @YourXML = '
    <zooid="1">
        <animals key="all" zebras="22" dogs="0" birds="4" />
        <animals key="all" workers="yes" vacation="occasion" />
    </zooid>
    <zooid="2">
        <animals key="house" zebras="0" dogs="1" birds="2" />
        <animals key="house" workers="no" vacation="no" />
    </zoodid>'

DECLARE @XML XML
SELECT @XML = 
    REPLACE(
          REPLACE(@YourXML, 'zooid=', 'zooid id=')
        , '</zoodid>'
        , '</zooid>')

SELECT 
      d.[Key]
    , Dogs = MAX(d.Dogs)
    , Zebras = MAX(d.Zebras)
    , Birds = MAX(d.Birds)
    , Workers = MAX(d.Workers)
    , Vacation = MAX(d.Vacation)
FROM (
    SELECT 
          [Key] = t.p.value('./@key', 'NVARCHAR(50)')
        , Zebras = t.p.value('./@zebras', 'INT')
        , Dogs = t.p.value('./@dogs', 'INT')
        , Birds = t.p.value('./@birds', 'INT')
        , Workers = t.p.value('./@workers', 'NVARCHAR(20)')
        , Vacation = t.p.value('./@vacation', 'NVARCHAR(20)')
    FROM @XML.nodes('/zooid/animals') t(p)
) d
GROUP BY d.[Key]

Upvotes: 1

djangojazz
djangojazz

Reputation: 13270

Your xml appears invalid. How are you able to specify an element like this: ? Generally xml structure is <(elementName) (Attribute)="(Value)"/>. Unless I am mistaken if you are casting text to xml the way it is it will fail. Saying that I can show a working example for proper xml in a self extracting example that will run in SQL Managment Studio as is.

declare @text1 varchar(max) = '<zooid="1"><animals="all" zebras="22" dogs="0" birds="4" /><animals="all" workers="yes" vacation="occasion" /></zooid>'
,   @text2 varchar(max) = '<a zooid="1"><b animals="all" zebras="22" dogs="0" birds="4" /><b animals="all" workers="yes" vacation="occasion" /></a>'
,   @xml xml
;

begin try 
    set @xml = cast(@text1 as xml)
end try
begin catch
    set @xml = '<ElementName Attribute="BadData Elements are not named" />'
end catch 

select @xml

begin try 
    set @xml = cast(@text2 as xml)
end try
begin catch
    set @xml = '<ElementName Attribute="BadData" />'
end catch 

select  
    @xml.value('(/a/b/@animals)[1]', 'varchar(20)') as AnimalsValue
,   @xml.value('(/a/b/@zebras)[1]', 'int') as ZebrasValue
,   @xml.value('(/a/b/@dogs)[1]', 'int') as DogsValue
,   @xml.value('(/a/b/@birds)[1]', 'int') as BirdsValue
,   @xml.value('(/a/b/@workers)[1]', 'varchar(16)') as Workers
,   @xml.value('(/a/b/@vacation)[1]', 'varchar(16)') as Vacation

The '.value' method is a syntax for querying xml in SQL. I am basically finding the elements(I did generics of a that contained b). Then once at the level I want '@animals' stands for 'attribute of name animals'. The [1] is a position since I can only return one thing at a time, so I chose the first position. Then it needs to a datatype to return. Text is varchar and numbers are ints.

XML query methods: http://msdn.microsoft.com/en-us/library/ms190798.aspx

Upvotes: 1

Related Questions