Parsing xml in SQL Server 2008 with multiple xml namespace

Question

I want to get gid and uid from the below xml. This xml exists as a column in a SQL Server 2008 table called xmlmsg. I need a SQL query that parses the table which has an xml column with the data like below:

<person xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns="http://api.money.com/schema/contact" Email="" 
        gId="11" uId="uadgra45678" Timestamp="2013-10-17T19:19:41Z" 
        xsi:schemaLocation="http://api.money.com/schema/contact http://api.money.com/schema/contact/contact-1.2.xsd">
</person>

Define parse. What is the expected output given this XML data? — har07
– har07, Commented Oct 10, 2014 at 0:46

Mark Hurd · Accepted Answer · 2014-10-10 01:32:13Z

1

(This is really a comment because it doesn't seem to answer the question implied by the title.)

This query (from LinqPad) creates and retrieves XML data:

--Drop Table #TempXml

Create Table #TempXml(Value XML)

Insert Into #TempXml(Value)Values('<person xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://api.money.com/schema/contact" Email=""
        gId="11" uId="uadgra45678" Timestamp="2013-10-17T19:19:41Z"
        xsi:schemaLocation="http://api.money.com/schema/contact http://api.money.com/schema/contact/contact-1.2.xsd">
</person>')

Select count(*) from #TempXml;

Select * from #TempXml;

Select Value.value('declare namespace AMC="http://api.money.com/schema/contact";
/AMC:person[1]/@uId','varchar(max)')
,Value.value('declare namespace AMC="http://api.money.com/schema/contact";
/AMC:person[1]/@gId','int')
From #TempXml;

Drop Table #TempXml

answered Oct 10, 2014 at 1:32

Mark Hurd

11k10 gold badges74 silver badges106 bronze badges

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

1 Comment

Mark Hurd Over a year ago

Here's a Stack Exchange Data Explorer query for the above snippet.

Collectives™ on Stack Overflow

Parsing xml in SQL Server 2008 with multiple xml namespace

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