SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select XML multiple nodes values into column		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ljp099
Yak Posting Veteran

79 Posts
Posted - 03/12/2013 :  20:17:50  Show Profile  Reply with Quote
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
Edited by - ljp099 on 03/12/2013 20:18:19

visakh16
Very Important crosS Applying yaK Herder

India
48064 Posts
Posted - 03/13/2013 :  00:49:58  Show Profile  Reply with Quote
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/
Go to Top of Page

ljp099
Yak Posting Veteran

79 Posts
Posted - 03/13/2013 :  08:52:31  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48064 Posts
Posted - 03/13/2013 :  10:18:39  Show Profile  Reply with Quote
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/
Go to Top of Page

ljp099
Yak Posting Veteran

79 Posts
Posted - 03/13/2013 :  10:29:51  Show Profile  Reply with Quote
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)?
Edited by - ljp099 on 03/13/2013 10:52:54
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000