How to get records from xml file

Posted by Krishnasamy2008 under Sql Server on 2/15/2011 | Points: 10 | Views : 2900 | Status : [Member] | Replies : 4
Hi Friends,

How to get records from xml file.I have below mentioned sample xml file.

DECLARE @xml XML;

SET @xml='<Country>
<State>
<StateID>1318</StateID>
<StateID>TN</StateName>
<Address>aaa,bbb.</Address>
<Population>2422222</Population>
<Profit>206281.00</Profit>
</State>
<State>
<StateID>1319</StateID>
<StateName>TN1</StateName>
<Address>aaa,bbb1.</Address>
<Population>24222221</Population>
<Profit>2062811.00</Profit>
</State>
<State>
<StateID>1320</StateID>
<StateID>TN2</StateName>
<Address>aaa,bbb2.</Address>
<Population>24222223</Population>
<Profit>2062813.00</Profit>
</State>
</Country>';


SELECT
Node.value('@StateID', 'INT'),
Node.value('@StateName', 'nvarchar(50)'),
Node.value('@Address', 'nvarchar(50)'),
Node.value('@Population', 'INT'),
Node.value('@Profit', 'decimal(18, 2)')


FROM @xml.nodes('/Country/State') TempXML (Node);

Currently it display's the empty records.Can anyone guide me.

Thanks and Regards,
Krishna.k




Responses

Posted by: Prabhakar on: 2/15/2011 [Member] [MVP] Starter | Points: 25

Up
0
Down

Hi Krishnasamy2008


check this url...it's a help full for you . .

http://www.c-sharpcorner.com/uploadfile/mahesh/readwritexmltutmellli2111282005041517am/readwritexmltutmellli21.aspx

Best Regard's
Prabhakar

Krishnasamy2008, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raja on: 2/15/2011 [Member] Starter | Points: 25

Up
0
Down
You can go through this article as well http://www.dotnetfunda.com/articles/article160.aspx.

However, you can also do this using LINQ.

Thanks

Regards,
Raja, USA

Krishnasamy2008, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 2/15/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Krishna.k,

Try this query in your sql server...
SELECT

tab.col.value( 'StateID[1]', 'INT'),
tab.col.value( 'StateName[1]', 'nvarchar(50)'),
tab.col.value( 'Address[1]', 'nvarchar(50)'),
tab.col.value( 'Population[1]', 'INT'),
tab.col.value( 'Profit[1]', 'decimal(18, 2)')
FROM @xml.nodes('/Country/State') tab(col)


and also small changes in your XML file...
you are using same tag name in different nodes..

DECLARE @xml XML;


SET @xml='<Country>
<State>
<StateID>1318</StateID>
<StateName>TN</StateName>
<Address>aaa,bbb.</Address>
<Population>2422222</Population>
<Profit>206281.00</Profit>
</State>
<State>
<StateID>1319</StateID>
<StateName>TN1</StateName>
<Address>aaa,bbb1.</Address>
<Population>24222221</Population>
<Profit>2062811.00</Profit>
</State>
<State>
<StateID>1320</StateID>
<StateName>TN</StateName>
<Address>aaa,bbb2.</Address>
<Population>24222223</Population>
<Profit>2062813.00</Profit>
</State>
</Country>';


Any more doubt ask me...

@Raja & @Prabhakar .. Krishna ask this question in SQL Server not in C#

Cheers :)

Thanks,
T.Saravanan

Krishnasamy2008, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vforvijay on: 2/16/2011 [Member] Starter | Points: 25

Up
0
Down
hi try this link it may helpful to you http://vbdotnetaddict.blogspot.com/2011/02/how-to-insert-xml-data-into-sql.html

Krishnasamy2008, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response