xml data get from select query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

xml data get from select query

Author

Topic

paritosh
Starting Member

42 Posts

Posted - 2014-06-19 : 15:41:51

Hi all ,

I want to convert xml data with select query and insert into table.

Xml sample like this :

<ROOT>
<ROW>
<NAME>JHON</NAME>
<ADDRESS>
<ADDRESS1>
<CITY>LKO</CITY>
<STATE>UP</STATE>
</ADDRESS1>
<ADDRESS2>
<CITY>DLI</CITY>
<STATE>DELHI</STATE>
</ADDRESS2>
</ADDRESS>
</ROW>
<ROW>
<NAME>YASH</NAME>
<ADDRESS>
<ADDRESS1>
<CITY>AAA</CITY>
<STATE>HYR</STATE>
</ADDRESS1>
</ROW>
</ROOT>

And data should be like this with help of select query :

NAME ADDRESS1CITY ADDRESS1STATE ADDRESS2CITY ADDRESS2STATE
JHON LKO UP DLI DELHI
YASH AAA HYR NULL NULL

I want simple query in form of above format if any one have better aprroach to do
please share.

Thank in advance...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-20 : 03:36:05

[code]DECLARE @Data XML = ' <ROOT>
<ROW>
<NAME>JHON</NAME>
<ADDRESS>
<ADDRESS1>
<CITY>LKO</CITY>
<STATE>UP</STATE>
</ADDRESS1>
<ADDRESS2>
<CITY>DLI</CITY>
<STATE>DELHI</STATE>
</ADDRESS2>
</ADDRESS>
</ROW>
<ROW>
<NAME>YASH</NAME>
<ADDRESS>
<ADDRESS1>
<CITY>AAA</CITY>
<STATE>HYR</STATE>
</ADDRESS1>
</ADDRESS>
</ROW>
</ROOT>';

-- SwePeso
SELECT r.n.value('(NAME[1])', 'VARCHAR(100)') AS [NAME],
r.n.value('(ADDRESS[1]/ADDRESS1[1]/CITY[1])', 'VARCHAR(100)') AS [ADDRESS1CITY],
r.n.value('(ADDRESS[1]/ADDRESS1[1]/STATE[1])', 'VARCHAR(100)') AS [ADDRESS1STATE],
r.n.value('(ADDRESS[1]/ADDRESS2[1]/CITY[1])', 'VARCHAR(100)') AS [ADDRESS2CITY],
r.n.value('(ADDRESS[1]/ADDRESS2[1]/STATE[1])', 'VARCHAR(100)') AS [ADDRESS2STATE]
FROM @Data.nodes('(/ROOT/ROW)') AS r(n);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

paritosh
Starting Member

42 Posts

Posted - 2014-06-22 : 15:33:07

Any other way to do this if yes, so give example...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-22 : 17:12:52

quote:
Originally posted by paritosh

Any other way to do this if yes, so give example...

Another way?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-22 : 17:45:28

quote:
Originally posted by paritosh

Any other way to do this if yes, so give example...

You can consider SwePeso's replies GOLD.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

paritosh
Starting Member

42 Posts

Posted - 2014-06-23 : 13:21:36

Hi SwePeso,

I do not know how many number of address add in address tag then how to handle in query .
Suppose this dynamic tag..
For example
Address1, address2, address3 and so on... then how we do this...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-23 : 14:27:37

Then you will need some kind of dynamic SQL.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

paritosh
Starting Member

42 Posts

Posted - 2014-06-24 : 01:18:33

So give example i do not know about this..

pls explain

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-24 : 04:09:54