parsing XML file with multiple NameSpace into SQL

Question

parsing XML file with multiple NameSpace into SQL

krishusavalia

SSCrazy

Points: 2385
More actions
January 28, 2011 at 10:33 am

#97627

below is the code i am using to read the data from the XML file.
I am getting error
Msg 2229, Level 16, State 1, Line 31
XQuery [nodes()]: The name "aaaaenv" does not denote a namespace.
Now how to declare the multiple NameSpace and read the Data from the XML?
DECLARE @x XML
SELECT @x='<aaaaenv:Envelope xmlns:aaaaenv="http://schemas.xmlaaaa.org/aaaa/envelope/"
xmlns:xsd="http://www.bbb.org/2002/XMLSchema"
xmlns:xsi="http://www.bbb.org/2002/XMLSchema-instance">
<aaaaenv:Body>
<ParentDataResponse type="New" xmlns="http://as.micheal.com/webservice/2007/06/longIslandexpress.xsd">
<Status>Successful</Status>
<typeID>1834041</typeID>
<Count>1</Count>
<ParentPackets>
<ParentPacket>
<ParentID>123456</CustomerID>
<EffectiveDate>2010-01-05</EffectiveDate>
<Child type="Child">
<FirstName>tommy</FirstName>
<LastName>devda</LastName>
<SSN>xxxxxxxx</SSN>
</Child>
</ParentPacket>
</ParentPackets>
</ParentDataResponse>
</aaaaenv:Body>
</aaaaenv:Envelope>'
;WITH XMLNAMESPACES(DEFAULT 'http://as.micheal.com/webservice/2007/06/longIslandexpress.xsd')
select
n.value('@type[1]','varchar(20)') as [Type],
n.value('Status[1]','varchar(20)') as [Status],
n.value('typeID[1]','varchar(20)') as typeID,
n.value('Count[1]','varchar(20)') as Count
FROM @x.nodes('aaaaenv:Envelope/aaaaenv:Body/ParentDataResponse') k(n)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

After some sample data cleanup (there's an invalid end tag in your sample) the following code did work.

The "trick" is to declare all namespaces used in your query. In the given case I added the namespace definition of aaaaenv.

;WITH XMLNAMESPACES(DEFAULT 'http://as.micheal.com/webservice/2007/06/longIslandexpress.xsd',

'http://schemas.xmlaaaa.org/aaaa/envelope/' as aaaaenv)

select

n.value('@type[1]','varchar(20)') as [Type],

n.value('Status[1]','varchar(20)') as [Status],

n.value('typeID[1]','varchar(20)') as typeID,

n.value('Count[1]','varchar(20)') as Count

FROM @x.nodes('aaaaenv:Envelope/aaaaenv:Body/ParentDataResponse') k(n)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

krishusavalia SSCrazy Points: 2385 More actions · Answer 2

Hey Man,

Thanks alot.

Its working.

But now I got the new file. Its has MoreProperty node under Child.

How can i write query to parse these into SQL server table.

I wanna out like,,,,table called More Property and the columns are property1,property2....

<Key>property1</Key>

<Value>aaaaaaaa</Value>

</Property>

<Key>property2</Key>

<Value>bbbbbbb</Value>

</Property>

<Key>property3</Key>

<Value>cccccc</Value>

</Property>

<Key>property4</Key>

<Value>dddddd</Value>

</Property>

<Key>propert5</Key>

<Value>eeeeeeee</Value>

</Property>

</MoreProperties>

LutzM SSC Guru Points: 107049 More actions · Answer 3

What have you tried so far and where did you get stuck?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

krishusavalia SSCrazy Points: 2385 More actions · Answer 4

Below is the Sample XMl file.

Now the problem is how can i give the node name for the property.there were like more than 50 propery in the mail file.and its all node name is same.How to parse it using the method you earlier mentioned?

DECLARE @x XML

SELECT @x='<aaaaenv:Envelope xmlns:aaaaenv="http://schemas.xmlaaaa.org/aaaa/envelope/"

xmlns:xsd="http://www.bbb.org/2002/XMLSchema"

xmlns:xsi="http://www.bbb.org/2002/XMLSchema-instance">

<aaaaenv:Body>

<Status>Successful</Status>

<FirstName>tommy</FirstName>

<LastName>devda</LastName>

<SSN>xxxxxxxx</SSN>

<Key>property1</Key>

<Value>aaaaaaaa</Value>

</Property>

<Key>property2</Key>

<Value>bbbbbbb</Value>

</Property>

<Key>property3</Key>

<Value>cccccc</Value>

</Property>

<Key>property4</Key>

<Value>dddddd</Value>

</Property>

<Key>propert5</Key>

<Value>eeeeeeee</Value>

</Property>

</MoreProperties>

</Child>

</ParentPacket>

</ParentPackets>

</ParentDataResponse>

</aaaaenv:Body>

</aaaaenv:Envelope>'

LutzM SSC Guru Points: 107049 More actions · Answer 5

It looks like you'Re not using XQuery that often.. So, here's a link to a great site providing a lot of examples about how to query XML data. Give it a try and post back if you get stuck.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

krishusavalia SSCrazy Points: 2385 More actions · Answer 6

Thanks,

I appriciate your response.

I already try to find the solution from the Jacob articles and tried different thing.

Let me give shot again.

krishusavalia SSCrazy Points: 2385 More actions · Answer 7

Below is the Code What i am trying to do.

I am stuck in the parsing the identity value of the parents to child node.

I know that i am doing something wrong into giving correct path for the XML query but i tried most of the things.

And have moreproperties, have a table structue but stucj into pivot that with identity value of the child table.

/*

create table #parentpackets

(

ParentPacketsID int identity(1,1),

ParentID int,

effectivedate date

)

create table #child

(

ChildID int identity(1,1),

ParentPacketsID int,

[Type] varchar(10),

FirstName varchar(10),

LastName varchar(10),

SSN int

)

Create table #MoreProperties

(

MorepropertiesID int identity(1,1),

ChildID int,

Property1 varchar(10),

Property2 varchar(10),

Property3 varchar(10),

Property4 varchar(10)

)

*/

declare @x xml

SELECT @x='<ParentPackets>

<FirstName>tommy</FirstName>

<LastName>devda</LastName>

<Key>property1</Key>

<Value>value1</Value>

</Property>

<Key>property2</Key>

<Value>value2</Value>

</Property>

<Key>property3</Key>

<Value>value3</Value>

</Property>

<Key>property4</Key>

<Value>value4</Value>

</Property>

</MoreProperties>

</Child>

</ParentPacket>

<LastName>desmond</LastName>

<Key>property1</Key>

<Value>value11</Value>

</Property>

<Key>property2</Key>

<Value>value22</Value>

</Property>

<Key>property3</Key>

<Value>value33</Value>

</Property>

<Key>property4</Key>

<Value>value44</Value>

</Property>

</MoreProperties>

</Child>

</ParentPacket>

<FirstName>gopmmy</FirstName>

<LastName>sephard</LastName>

<Key>property1</Key>

<Value>value111</Value>

</Property>

<Key>property2</Key>

<Value>value222</Value>

</Property>

<Key>property3</Key>

<Value>value333</Value>

</Property>

<Key>property4</Key>

<Value>value444</Value>

</Property>

</MoreProperties>

</Child>

</ParentPacket>

</ParentPackets>'

declare @parentID table (ParentpacketsID int,ParentID int)

insert into #parentpackets

output inserted.ParentPacketsID,inserted.ParentID

into @parentID

select

n.value('ParentID[1]','INT') as ParentID,

n.value('EffectiveDate[1]','date')as EffectiveDate

from @x.nodes('ParentPackets/ParentPacket') m(n)

insert into #child

select

a.ParentpacketsID,

m.value('@type[1]','varchar(10)') as type,

m.value ('FirstName[1]','varchar(10)')as FirstName,

m.value('LastName[1]','varchar(10)')as LastName,

m.value('SSN[1]','int')as SSN

from @x.nodes('ParentPackets/ParentPacket') m(n)

cross apply m.n.nodes('Child') l(m)

cross apply @parentID as a

where a.ParentID=n.value('@ParentID[1]','int')

/*

select * from #parentpackets

select * from #child

select * from #MoreProperties

drop table #parentpackets

drop table #child

drop table #MoreProperties

*/

LutzM SSC Guru Points: 107049 More actions · Answer 8

The issue is rather simple:

Replace

where a.ParentID=n.value('@ParentID[1]','int') with

where a.ParentID=n.value('ParentID[1]','int')

ParentID is an element, but you've referenced it as an attribute.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

krishusavalia SSCrazy Points: 2385 More actions · Answer 9

Thanks for your response.

I figured out it earlier but still i am stuck into how to parse the moreproperties value into the table.

If you shed any lights on it.

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 10

You'd need to add the MoreProperties/Property node e.g. by using another CROSS APPLY.

If you really need to store it in the table structure as provided, you'd need to CrossTab (or pivot) the data as shown.

However, I strongly recommend to reevaluate your table design regarding normalization. Instead of four columns all holding Property values, you should consider a single column (as shown when using just the inner query from the cte).

;

WITH cte AS

(

SELECT

a.ParentpacketsID,

ROW_NUMBER() OVER(PARTITION BY a.ParentpacketsID ORDER BY (SELECT 1)) AS col,

c.value ('Key[1]','varchar(10)')AS PropertyNo,

c.value ('Value[1]','varchar(10)')AS PropertyValue

FROM @x.nodes('ParentPackets/ParentPacket') m(n)

CROSS APPLY m.n.nodes('Child') l(m)

CROSS APPLY l.m.nodes('MoreProperties/Property') T(c)

CROSS APPLY @parentID AS a

WHERE a.ParentID=n.value('ParentID[1]','int')

)

SELECT

ParentpacketsID,

MAX(CASE WHEN col=1 THEN PropertyValue ELSE NULL END) AS Property1,

MAX(CASE WHEN col=2 THEN PropertyValue ELSE NULL END) AS Property2,

MAX(CASE WHEN col=3 THEN PropertyValue ELSE NULL END) AS Property3,

MAX(CASE WHEN col=4 THEN PropertyValue ELSE NULL END) AS Property4,

MAX(CASE WHEN col=5 THEN PropertyValue ELSE NULL END) AS Property5

FROM cte

GROUP BY ParentpacketsID

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

krishusavalia SSCrazy Points: 2385 More actions · Answer 11

I appriciate your response. And you solution was awesome. Thats what i was looking for.

And I Appriciate you suggetion that I have to reevaluate the table structure but In my actual XML file I am getting more that 100 distinct Properties value in MoreProperties.

I will appriciate your suggestion,,If you have any better thought regarding the structure.

Thanks Again

LutzM SSC Guru Points: 107049 More actions · Answer 12

krishusavalia (2/3/2011)
I appriciate your response. And you solution was awesome. Thats what i was looking for.
And I Appriciate you suggetion that I have to reevaluate the table structure but In my actual XML file I am getting more that 100 distinct Properties value in MoreProperties.
I will appriciate your suggestion,,If you have any better thought regarding the structure.
Thanks Again

A table structure based on the result of the following query would be more flexible if there will ever be a change in the number of properties (think of max(property +1)). With the current design the following changes would be required:

- add another column to the table

- change the code to fill that column

- bring it to the limit: as soon as you'd have to insert the 670th property (roughly), you'd even have to create a separate table, since 2K5 would only allow 8060 bytes per row including the handling within the code and all queries/views etc..

My recommended design would be something like:

Create table #MoreProperties

(

MorepropertiesID int identity(1,1),

ChildID int,

PropertyPos SMALLINT, -- maybe this column isn't even needed by the processes. It depends.

PropertyNo varchar(10),

PropertyValue varchar(10)

)

If you'd need the data in a pivoted format, you could either do it at the presentation layer (recommended), or using the DynamicCrossTab approach (for details please see the related link in my signature).

With such a design you wouldn't have to worry about additional properties nor would you have empty columns for childs with less than max properties.

SELECT

a.ParentpacketsID,

ROW_NUMBER() OVER(PARTITION BY a.ParentpacketsID ORDER BY (SELECT 1)) AS col,

c.value ('Key[1]','varchar(10)')AS PropertyNo,

c.value ('Value[1]','varchar(10)')AS PropertyValue

FROM @x.nodes('ParentPackets/ParentPacket') m(n)

CROSS APPLY m.n.nodes('Child') l(m)

CROSS APPLY l.m.nodes('MoreProperties/Property') T(c)

CROSS APPLY @parentID AS a

WHERE a.ParentID=n.value('ParentID[1]','int')

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

krishusavalia SSCrazy Points: 2385 More actions · Answer 13

Hey Thanks man for your recommandation.

I will try it in my secinario.

Thanks Again