?
Solved

SQL XML SPLIT

Posted on 2012-09-05
7
Medium Priority
?
555 Views
Last Modified: 2012-09-06
Here is an XML that needs to be split into 2 (Employees & Contractors)

<Root>
    <Employees>
        <Employee>
            <Name>John Doe</NAME>
        </Employee>
        <Employee>
            <Name>John Doe</NAME>
        </Employee>
    </Employees>
    <Contractors>
        <Contractor>
            <Name>James Doe</NAME>
        </Contracts>
    </Contractors>
</Root>

It needs to be split like
@EmployeeXML =
'<Employees>
        <Employee>
            <Name>John Doe</NAME>
        </Employee>
        <Employee>
            <Name>John Doe</NAME>
        </Employee>
    </Employees>'

@ContractorXML =
  <Contractors>
        <Contractor>
            <Name>James Doe</NAME>
        </Contracts>
    </Contractors>

How to split it in to 2 Variables in SQL?
0
Comment
Question by:sansoftura
  • 3
  • 3
7 Comments
 
LVL 2

Expert Comment

by:exoduster
ID: 38371509
Where do you have this xml? At column of varchar type? Or just at variable? What is the data type of this variable then?
0
 
LVL 6

Author Comment

by:sansoftura
ID: 38372062
The root XML comes in as a patameter to SP, I have to split it into 2 with in a SP
0
 
LVL 2

Expert Comment

by:exoduster
ID: 38372168
And parameter is type of? VARCHAR? XML? Or the other?
And those two variables at which you want to have splitted xml will be of which type?
Sorry for my eanglish...
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 6

Author Comment

by:sansoftura
ID: 38372183
OK the parameter is XML, but can be converted to VARCHAR. The output 2 variables can be VARCHAR
0
 
LVL 2

Expert Comment

by:exoduster
ID: 38372219
If its VARCHAR and you dont need indentation you can just use CHARINDEX and STUFF string functions. If you dont know how i can write this... But what about sql server books online?
0
 
LVL 30

Accepted Solution

by:
Rich Weissler earned 2000 total points
ID: 38372336
Not an expert, but XML is one of the SQL areas I've known I need to investigate more fully.

One possible solution may be to use query() off the XML variable.

If, for example, you have the first xml loading in an XML variable @root2Bsplit, then

"Select @root2Bsplit.query('Root/Employees')" should return your employees
and
"Select @root2Bsplit.query('Root/Contractors')" should return your contractors.

If that doesn't wok, please let me know.
0
 
LVL 6

Author Closing Comment

by:sansoftura
ID: 38372424
Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question