MS SQL XML - New to XML

Author

New to XML

footyplayer

2005-09-15, 8:24 pm

Hi All,

I'm VERY new to XML and as such am still learning. However, I think the
problem I'm having should be fairly simple to solve. My XML file is laid
out as such:

<CompanyList>
<Company>
<ClientID>YOCO01</ClientID>
<Name>YOUR COMPANY LTD."</Name>
</Company>
<Company>
<ClientID>TECO01</ClientID>
<Name>TEST COMPANY INC.</Name>
</Company>
</CompanyList>

I use XPath to retrieve values from XML files for display in an application
I wrote. I need to know, what is the proper XPath Query to retrieve the
ClientID for a given Name? For example, I want to retrieve YOCO01 when I
submit YOUR COMPANY LTD. as the search name.

Also, while we're on this topic, does anyone have any suggestions for my
layout? Or is this a proper layout for XML files? (Again, VERY new) Any
help is greatly appreciated.

Thanks In Advance,
Brad

Han

2005-09-16, 3:24 am

When a <Name> is given, xpaths to access the preceding sibling <ClientID>
are,

/descendant::Company[Name="YOUR COMPANY LTD"]/ClientID
or
/descendant::Company/Name[.="YOUR COMPANY LTD"]/preceding-sibling::ClientID

or a lot more ways to do that.

Your XML looks nice for me. Readable and easy. Sometimes attributes are more
efficient than elements, but not quite important.

Where do you save your XML? SQL2000, SQL2005, or file, ... ?

--
Pohwan Han. Seoul. Have a nice day.
"footyplayer" < anonymous@discussion
s.microsoft.com> wrote in message
news:%23aSnshkuFHA.3740@TK2MSFTNGP14.phx.gbl...
> Hi All,
>
> I'm VERY new to XML and as such am still learning. However, I think the
> problem I'm having should be fairly simple to solve. My XML file is laid
> out as such:
>
> <CompanyList>
> <Company>
> <ClientID>YOCO01</ClientID>
> <Name>YOUR COMPANY LTD."</Name>
> </Company>
> <Company>
> <ClientID>TECO01</ClientID>
> <Name>TEST COMPANY INC.</Name>
> </Company>
> </CompanyList>
>
> I use XPath to retrieve values from XML files for display in an
> application I wrote. I need to know, what is the proper XPath Query to
> retrieve the ClientID for a given Name? For example, I want to retrieve
> YOCO01 when I submit YOUR COMPANY LTD. as the search name.
>
> Also, while we're on this topic, does anyone have any suggestions for my
> layout? Or is this a proper layout for XML files? (Again, VERY new) Any
> help is greatly appreciated.
>
> Thanks In Advance,
> Brad
>

Michael Rys [MSFT]

2005-09-21, 3:24 am

Your XML looks fine.

What technology are you using to read the data with XPath?

If you use SQL Server 2005 and have the XML in an XML datatype such as the
variable @x, you would write:

declare @name nvarchar(100)
declare @x xml

set @x = ....

select @name = N'YOUR COMPANY LTD.'
select @x.value(/Company[Name = sql:variable("@name")]/@ClientID',
'nvarchar(20)')

Best regards
Michael

"footyplayer" < anonymous@discussion
s.microsoft.com> wrote in message
news:%23aSnshkuFHA.3740@TK2MSFTNGP14.phx.gbl...
> Hi All,
>
> I'm VERY new to XML and as such am still learning. However, I think the
> problem I'm having should be fairly simple to solve. My XML file is laid
> out as such:
>
> <CompanyList>
> <Company>
> <ClientID>YOCO01</ClientID>
> <Name>YOUR COMPANY LTD."</Name>
> </Company>
> <Company>
> <ClientID>TECO01</ClientID>
> <Name>TEST COMPANY INC.</Name>
> </Company>
> </CompanyList>
>
> I use XPath to retrieve values from XML files for display in an
> application I wrote. I need to know, what is the proper XPath Query to
> retrieve the ClientID for a given Name? For example, I want to retrieve
> YOCO01 when I submit YOUR COMPANY LTD. as the search name.
>
> Also, while we're on this topic, does anyone have any suggestions for my
> layout? Or is this a proper layout for XML files? (Again, VERY new) Any
> help is greatly appreciated.
>
> Thanks In Advance,
> Brad
>

Sponsored Links