Parsing XML using TSQL

Question

I'm trying to parse out the following XML with TSQL:

<Response xmlns="http://data.fcc.gov/api" status="OK" executionTime="9">
    <Block FIPS="181770103002004" />
    <County FIPS="18177" name="Wayne" />
    <State FIPS="18" code="IN" name="Indiana" />
</Response>

Using the following script:

SELECT x.i.value('@name', 'varchar(200)') AS county
FROM @xml.nodes('Response/County') AS x(i)

But I get no results, any help as to what I'm doing wrong would be greatly appreciated.

Thanks!

Jerrad · Accepted Answer · 2014-06-24 15:26:58Z

4

Your XML namespace is messing things up. Either remove the xmlns="http://data.fcc.gov/api" from the Response element, or prefix your query with WITH XMLNAMESPACES ( DEFAULT 'http://data.fcc.gov/api')

;WITH XMLNAMESPACES ( DEFAULT 'http://data.fcc.gov/api')
SELECT x.i.value('@name', 'varchar(200)') AS county
FROM @xml.nodes('Response/County') AS x(i)

Or you can use wildcard namespaces in the query:

SELECT x.i.value('@name', 'varchar(200)') AS county
FROM @xml.nodes('*:Response/*:County') AS x(i)

answered Jun 24, 2014 at 15:26

Jerrad

5,3001 gold badge20 silver badges23 bronze badges

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

1 Comment

DeeBeeEh Over a year ago

Right on the nose. Both of those suggestions worked. I ended up removing the xmlns element and my script worked. Thanks for the quick reply.

Bogdan · Accepted Answer · 2014-06-24 15:22:26Z

0

You can do it using OPENXML like this:

DECLARE @idoc INT
DECLARE @xml AS XML  = 

'<Response xmlns="http://data.fcc.gov/api" status="OK" executionTime="9">
    <Block FIPS="181770103002004" />
    <County FIPS="18177" name="Wayne" />
    <State FIPS="18" code="IN" name="Indiana" />
</Response>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml, N'<root xmlns:n="http://data.fcc.gov/api" />'

SELECT
    Name AS County
FROM OPENXML (@idoc, '/n:Response/n:County', 1)
WITH
    (
        Name VARCHAR(255) '@name'
    )

EXEC sp_xml_removedocument @idoc
GO

answered Jun 24, 2014 at 15:22

Bogdan

11 silver badge1 bronze badge

Collectives™ on Stack Overflow

Parsing XML using TSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related