Pls help. new to xml.

If an assembly Box consists of Parts listed how do I retrieve record set
that lists
Box and all its parts as
Assembly Part
-- --
Box 1-400-53
Box 1-1-45
Box 1-2-47
and so on.
Thank you so much for your help.
Farmer
DECLARE @.XmlDocumentHandle int
DECLARE @.XmlDocument nvarchar(1000)
SET @.XmlDocument = N'<ROOT>
<Assembly>
<Name>Box</Name>
<Part>1-400-53</Part>
<Part>1-1-45</Part>
<Part>1-2-47</Part>
<Part>1-802-57</Part>
<Part>1-300-48</Part>
<Part>1-851-55</Part>
<Part>1-850-54</Part>
<Part>1-800-52</Part>
<Part>1-801-50</Part>
<Part>1-700-56</Part>
<Part>1-1260-49</Part>
<Part>1-1260-44</Part>
<Part>1-1260-46</Part>
<Part>1-1260-58</Part>
<Part>1-1260-51</Part>
</Assembly>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.XmlDocumentHandle OUTPUT, @.XmlDocument
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML(@.XmlDocumentHandle, N'/ROOT/Assembly', 2)
WITH (
AssName varchar(25) 'Name'
,Part varchar(25) 'Part'
)
EXEC sp_xml_removedocument @.XmlDocumentHandleHello Farmer,

> If an assembly Box consists of Parts listed how do I retrieve record
> set that lists
Um, you cheat... :)
SELECT *
FROM OPENXML(@.XmlDocumentHandle, N'/ROOT/Assembly/Part', 2)
WITH (
AssName varchar(25) '../Name',
Part varchar(25) '.'
)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||I can't pass my judgement if it is "cheating" but it works. Thank you.
If understand it correctly, you extended path to read the dataset based on
Part as a tag instead of Assembly.
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74273e18c82ad3ef4dd330@.news.microsoft.com...
> Hello Farmer,
>
> Um, you cheat... :)
> SELECT *
> FROM OPENXML(@.XmlDocumentHandle, N'/ROOT/Assembly/Part', 2)
> WITH (
> AssName varchar(25) '../Name',
> Part varchar(25) '.'
> )
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi,
Actually, you should use this method because you need to navigate to the
element that should be mapped to a row in the row pattern
Otherwise, openxml only recognize one subelement.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
from the web interface: Partner Feedback
from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--
<b87ad74273e18c82ad3ef4dd330@.news.microsoft.com>
on|||Hi
How do I implement it the other way round; i.e. convert SQL Column into
a XML type?|||Hi,
Since our Newsgruop is an issue based support. Would you please open a new
post and describe your concern in detail. We are glad to be of assistance.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--
19:49:23 GMT)
.NET CLR 1.1.4322; .NET CLR 2.0. 50727),gzip(gfe),gzip(gfe)[color=darkred
]
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTFEEDS01.phx.gbl!newsfeed00
.sul.t-online.de!t-online.de!border2.nntp.dca.giganews.com!border1.nntp.dca.
giganews.com!nntp.giganews.com!postnews.google.com!v46g2000cwv.googlegroups.
com!not-for-mail|||Look at the FOR XML functionality...
Best regards
Michael
<prabhupr@.hotmail.com> wrote in message
news:1144871356.496917.315650@.v46g2000cwv.googlegroups.com...
> Hi
> How do I implement it the other way round; i.e. convert SQL Column into
> a XML type?
>