OPENXML to accept both attributes and elements

9 Feb 2009 5:59 PM

Goran Djuranovic

Hi all,
Is it possible to retreive values from XML document whether they where sent
as elements or attributes, using OPENXML?
For example, if someone sends me:

<Root><Customer Name="blah"></Customer></Root> or
<Root><Customer><Name>blah</Name></Customer></Root>

I want to be able to read both. Possible?

Thx
Goran

9 Feb 2009 6:12 PM

Martin Honnen

Goran Djuranovic wrote:

> Is it possible to retreive values from XML document whether they where sent
> as elements or attributes, using OPENXML?
> For example, if someone sends me:
>
> <Root><Customer Name="blah"></Customer></Root> or
> <Root><Customer><Name>blah</Name></Customer></Root>
>
> I want to be able to read both. Possible?

I am not sure about OPENXML but XQuery can do it:

DECLARE @doc XML;
SET @doc = N'<Root><Customer
Name="Foo"></Customer><Customer><Name>Bar</Name></Customer></Root>';

SELECT
T.c.value('(@Name, Name)[1]', 'nvarchar(20)') AS [Name]
FROM
@doc.nodes('Root/Customer') AS T(c);

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

9 Feb 2009 7:36 PM

Goran Djuranovic

Sorry Martin, I am using SQL 2000, which means no XML datatype available.
Any other suggestions? Thx

Show quoteHide quote

"Martin Honnen" <mahotr***@yahoo.de> wrote in message
news:%23pg6vHuiJHA.4868@TK2MSFTNGP02.phx.gbl...
> Goran Djuranovic wrote:
>
>> Is it possible to retreive values from XML document whether they where
>> sent as elements or attributes, using OPENXML?
>> For example, if someone sends me:
>>
>> <Root><Customer Name="blah"></Customer></Root> or
>> <Root><Customer><Name>blah</Name></Customer></Root>
>>
>> I want to be able to read both. Possible?
>
> I am not sure about OPENXML but XQuery can do it:
>
> DECLARE @doc XML;
> SET @doc = N'<Root><Customer
> Name="Foo"></Customer><Customer><Name>Bar</Name></Customer></Root>';
>
> SELECT
> T.c.value('(@Name, Name)[1]', 'nvarchar(20)') AS [Name]
> FROM
> @doc.nodes('Root/Customer') AS T(c);
>
>
>
> --
>
> Martin Honnen --- MVP XML
> http://JavaScript.FAQTs.com/

9 Feb 2009 9:06 PM

Bob

It is possible, something like this:

USE tempdb
GO

DECLARE @intDoc int
DECLARE @chvXMLDoc varchar(8000)
-- sample XML document
SET @chvXMLDoc ='<Root><Customer Name="blah1"></Customer>
<Customer><Name>blah2</Name></Customer></Root>'

--Load the XML document into memory.
EXEC sp_xml_preparedocument @intDoc OUTPUT, @chvXMLDoc

SELECT *
FROM OPENXML ( @intDoc, '/Root/Customer', 8 )
WITH ( Name VARCHAR(30) '@Name' )

SELECT *
FROM OPENXML ( @intDoc, '/Root/Customer', 8 )
WITH ( Name VARCHAR(30) 'Name' )

EXEC sp_xml_removedocument @intDoc
GO

HTH
wBob
Rate the post

Show quoteHide quote

"Goran Djuranovic" wrote:

> Hi all,
> Is it possible to retreive values from XML document whether they where sent
> as elements or attributes, using OPENXML?
> For example, if someone sends me:
>
> <Root><Customer Name="blah"></Customer></Root> or
> <Root><Customer><Name>blah</Name></Customer></Root>
>
> I want to be able to read both. Possible?
>
> Thx
> Goran
>
>
>