Select XML multiple nodes values into column

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

SQL Server 2008 Forums

Transact-SQL (2008)

Select XML multiple nodes values into column

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2013-03-12 : 20:17:50

I have this xml data:


<root>
    <DocInfo>
        <CompanyName>Some Company</CompanyName>
        <WebsiteUrl>http://www.someurl.com</WebsiteUrl>
        <PrimaryServices>Benefits Administration</PrimaryServices>
        <PrimaryServices>Payroll Processing</PrimaryServices>
        <SecondaryServices>Background Checking</SecondaryServices>
        <SecondaryServices>HR Outsourcing</SecondaryServices>
    </DocInfo>
   <DocInfo>
        <CompanyName>Some Company</CompanyName>
        <WebsiteUrl>http://www.someurl.com</WebsiteUrl>
        <PrimaryServices>Benefits Administration</PrimaryServices>
        <PrimaryServices>Payroll Processing</PrimaryServices>
        <SecondaryServices>Background Checking</SecondaryServices>
        <SecondaryServices>HR Outsourcing</SecondaryServices>
    </DocInfo>
</root>

and am using this sql to retrieve the single node values:


select  @xmlString = my xml string
set @xmlString = replace(@xmlString,'<?xml version="1.0" encoding="UTF-16" standalone="yes"?>','')
set @XML = cast(@xmlString as xml)

Select
T.N.value('CompanyName[1]', 'varchar(250)') as CompanyName,
T.N.value('WebsiteUrl[1]', 'varchar(250)') as WebsiteUrl,
T.N.value('PrimaryServices[1]', 'varchar(250)') as PrimaryServices,
T.N.value('SecondaryServices[1]', 'varchar(250)') as SecondaryServices,
T.N.value('Description[1]', 'varchar(max)') as Description
from @XML.nodes('/root/DocInfo') as T(N)

This works fine for the single node values (CompanyName, WebsiteUrl). However, it isnt inserting the nodes with multiple values properly (like PrimaryServices and SecondaryServices). How do I get the multiple node values into these columns?

Thanks for any help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-13 : 00:49:58

how do you want them to come? As single value or as multiple rows?

Based on that it may be like


Select
T.N.value('CompanyName[1]', 'varchar(250)') as CompanyName,
T.N.value('WebsiteUrl[1]', 'varchar(250)') as WebsiteUrl,
T.N.query('concat(string(PrimaryServices[1]),",",string(PrimaryServices[2]))') as PrimaryServices,
T.N.query('concat(string(SecondaryServices[1]),",",string(SecondaryServices[2]))') as SecondaryServices,
T.N.value('Description[1]', 'varchar(max)') as Description
from @x.nodes('/root/DocInfo') as T(N)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ljp099
Yak Posting Veteran

79 Posts

Posted - 2013-03-13 : 08:52:31

I truncated the xml code I have to parse to save space here. The primary and secondary services nodes are not fixed in size but are derived from checkbox lists and vary in size anywhere from zero to sixteen. What would be the method of dynamically retrieving these values?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-13 : 10:18:39

you didnt still specify how you want values to come. You want them as separate rows or in same row?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ljp099
Yak Posting Veteran

79 Posts

Posted - 2013-03-13 : 10:29:51

Single value delimiting would be fine.

Is there a method that won't require me to specify the array number and will automatically detect the nodes, retrieve the values and insert the delimiters accordingly?

Instead of this:
T.N.query('concat(string(PrimaryServices[1]),",",string(PrimaryServices[2]))') as PrimaryServices

Something that will find all the PrimaryServices nodes (0-X)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources