issue with xml parsing data to sql query

Question

I am working on SQL query where i need to parse xml to sql .Query is working for other xml but not working for below xml. query i am using is as below

DECLARE @xmldata XML 
SET @xmldata =
N'<SearchProductsResponse xmlns="http://api.abc.com/">
<productItems>
<productItem id="5d0ee86d84bcc5edef43236d61419a59">
<trackingLinks>
<trackingLink adspaceId="100">
<ppv>
 abc.com
</ppv>
<ppc>
abc.com
</ppc>
</trackingLink>
</trackingLinks>
</productItem>
</productItems>
</SearchProductsResponse>';

select
    t1.c.value('@id', 'varchar(300)') as itemid,
    c.c.value('@id', 'int') as adspaceId
from 
    @xmldata.nodes('*:SearchProductsResponse/*:productItems/*:productItem') as t1(c)
    OUTER APPLY t1.c.nodes('*:trackingLinks/*:trackingLink') as c(c)

Output I am getting is

itemid                            adspaceId
5d0ee86d84bcc5edef43236d61419a59    NULL

But I should return 100 instead of NULL . This query is working for other XML but don't know whats wrong with this XML. I have double check XML and query nothing different from other XML. Hope I am not missing some silly mistake

Brent D · Accepted Answer · 2015-08-01 17:53:11Z

1

Change the attribute selection for your adspaceId column to @adspaceId.

select
t1.c.value('@id', 'varchar(300)') as itemid,
c.c.value('@adspaceId', 'int') as adspaceId
from @xmldata.nodes('*:SearchProductsResponse/*:productItems/*:productItem') as t1(c)
OUTER APPLY t1.c.nodes('*:trackingLinks/*:trackingLink') as c(c)

answered Aug 1, 2015 at 17:53

Brent D

9085 silver badges16 bronze badges

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

1 Comment

Mahajan344 Over a year ago

Oh.. I knew that its silly mistake from my side.. Thanks for your help

Collectives™ on Stack Overflow

issue with xml parsing data to sql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related