OPENXML to return actual raw XML

Hello there,
I have the following Transact-SQL code. I want to return the raw <Features>
node and its content (tags included), but the OPENXML will always return
text values only.
Any suggestions or ideas are welcome... (you can paste this code into the
Query Analyzer to see what i mean).
Thanks!
DECLARE @.h int
EXEC sp_xml_preparedocument @.h OUTPUT,
N'
<?xml version="1.0"?>
<MovexOrder xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Items>
<MovexItem>
<Features>
<FechaEntrada>28/09/2004 15:04:11</FechaEntrada>
<FechaSalida>16/10/2004 15:04:11</FechaSalida>
<Modelo>A00600</Modelo>
<TallaAmericana>897</TallaAmericana>
<TallaPantalon>77</TallaPantalon>
<RefTejido>oiljgkkk</RefTejido>
<Observaciones>texto</Observaciones>
<LargoPrenda />
<LargoManga />
</Features>
<ID>1</ID>
<ITDS />
<ITNO>A00600HH9005148 </ITNO>
<ORTP />
<JDCD>001</JDCD>
<ORQT>2</ORQT>
<SAPR>0</SAPR>
<PONR>0</PONR>
<MODEL6>A00600</MODEL6>
<MODEL2>HH</MODEL2>
<SERIES>900</SERIES>
<TYPE>H</TYPE>
<COLOR>51</COLOR>
<SIZE>T48 </SIZE>
<Text />
</MovexItem>
<MovexItem>
<ID>2</ID>
<ITDS />
<ITNO>A00600HH9005154 </ITNO>
<ORTP />
<JDCD>001</JDCD>
<ORQT>1</ORQT>
<SAPR>0</SAPR>
<PONR>0</PONR>
<MODEL6>A00600</MODEL6>
<MODEL2>HH</MODEL2>
<SERIES>900</SERIES>
<TYPE>H</TYPE>
<COLOR>51</COLOR>
<SIZE>T54 </SIZE>
<Text />
</MovexItem>
</Items>
<ORNO />
<CONO>400</CONO>
<WHLO />
<CUNO>H0600001 </CUNO>
<CUNM />
<CMPN>05PV</CMPN>
<ORTP>NOR</ORTP>
<FACI>A01</FACI>
<CUOR />
<ORDT>28/09/2004</ORDT>
<RLDT>14/01/2005</RLDT>
<OFNO>ENE</OFNO>
<ORST>0</ORST>
<STAT />
<ADID />
<ADDR />
<ID>-1</ID>
<Text />
</MovexOrder>'
SELECT ID, Features
FROM OpenXML(@.h,'/MovexOrder/Items/MovexItem', 2)
WITH (ID INT, Features ntext )
WHERE Features IS NOT NULL
Change the SELECT statement to:
SELECT ID, Features
FROM OpenXML(@.h,'/MovexOrder/Items/MovexItem/Features', 2)
WITH
(ID INT '../ID',
Features ntext '@.mp:xmltext' )
WHERE Features IS NOT NULL
Cheerrs,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Ariel" <Ariel@.discussions.microsoft.com> wrote in message
news:8CC6DA6E-C06E-4499-BE40-42F5CFEC2FFC@.microsoft.com...
Hello there,
I have the following Transact-SQL code. I want to return the raw <Features>
node and its content (tags included), but the OPENXML will always return
text values only.
Any suggestions or ideas are welcome... (you can paste this code into the
Query Analyzer to see what i mean).
Thanks!
DECLARE @.h int
EXEC sp_xml_preparedocument @.h OUTPUT,
N'
<?xml version="1.0"?>
<MovexOrder xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Items>
<MovexItem>
<Features>
<FechaEntrada>28/09/2004 15:04:11</FechaEntrada>
<FechaSalida>16/10/2004 15:04:11</FechaSalida>
<Modelo>A00600</Modelo>
<TallaAmericana>897</TallaAmericana>
<TallaPantalon>77</TallaPantalon>
<RefTejido>oiljgkkk</RefTejido>
<Observaciones>texto</Observaciones>
<LargoPrenda />
<LargoManga />
</Features>
<ID>1</ID>
<ITDS />
<ITNO>A00600HH9005148 </ITNO>
<ORTP />
<JDCD>001</JDCD>
<ORQT>2</ORQT>
<SAPR>0</SAPR>
<PONR>0</PONR>
<MODEL6>A00600</MODEL6>
<MODEL2>HH</MODEL2>
<SERIES>900</SERIES>
<TYPE>H</TYPE>
<COLOR>51</COLOR>
<SIZE>T48 </SIZE>
<Text />
</MovexItem>
<MovexItem>
<ID>2</ID>
<ITDS />
<ITNO>A00600HH9005154 </ITNO>
<ORTP />
<JDCD>001</JDCD>
<ORQT>1</ORQT>
<SAPR>0</SAPR>
<PONR>0</PONR>
<MODEL6>A00600</MODEL6>
<MODEL2>HH</MODEL2>
<SERIES>900</SERIES>
<TYPE>H</TYPE>
<COLOR>51</COLOR>
<SIZE>T54 </SIZE>
<Text />
</MovexItem>
</Items>
<ORNO />
<CONO>400</CONO>
<WHLO />
<CUNO>H0600001 </CUNO>
<CUNM />
<CMPN>05PV</CMPN>
<ORTP>NOR</ORTP>
<FACI>A01</FACI>
<CUOR />
<ORDT>28/09/2004</ORDT>
<RLDT>14/01/2005</RLDT>
<OFNO>ENE</OFNO>
<ORST>0</ORST>
<STAT />
<ADID />
<ADDR />
<ID>-1</ID>
<Text />
</MovexOrder>'
SELECT ID, Features
FROM OpenXML(@.h,'/MovexOrder/Items/MovexItem', 2)
WITH (ID INT, Features ntext )
WHERE Features IS NOT NULL
|||Thanks a lot Graeme! It's working now.
"Graeme Malcolm" wrote:

> Change the SELECT statement to:
> SELECT ID, Features
> FROM OpenXML(@.h,'/MovexOrder/Items/MovexItem/Features', 2)
> WITH
> (ID INT '../ID',
> Features ntext '@.mp:xmltext' )
> WHERE Features IS NOT NULL
> Cheerrs,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Ariel" <Ariel@.discussions.microsoft.com> wrote in message
> news:8CC6DA6E-C06E-4499-BE40-42F5CFEC2FFC@.microsoft.com...
> Hello there,
> I have the following Transact-SQL code. I want to return the raw <Features>
> node and its content (tags included), but the OPENXML will always return
> text values only.
> Any suggestions or ideas are welcome... (you can paste this code into the
> Query Analyzer to see what i mean).
> Thanks!
>
> DECLARE @.h int
> EXEC sp_xml_preparedocument @.h OUTPUT,
> N'
> <?xml version="1.0"?>
> <MovexOrder xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <Items>
> <MovexItem>
> <Features>
> <FechaEntrada>28/09/2004 15:04:11</FechaEntrada>
> <FechaSalida>16/10/2004 15:04:11</FechaSalida>
> <Modelo>A00600</Modelo>
> <TallaAmericana>897</TallaAmericana>
> <TallaPantalon>77</TallaPantalon>
> <RefTejido>oiljgkkk</RefTejido>
> <Observaciones>texto</Observaciones>
> <LargoPrenda />
> <LargoManga />
> </Features>
> <ID>1</ID>
> <ITDS />
> <ITNO>A00600HH9005148 </ITNO>
> <ORTP />
> <JDCD>001</JDCD>
> <ORQT>2</ORQT>
> <SAPR>0</SAPR>
> <PONR>0</PONR>
> <MODEL6>A00600</MODEL6>
> <MODEL2>HH</MODEL2>
> <SERIES>900</SERIES>
> <TYPE>H</TYPE>
> <COLOR>51</COLOR>
> <SIZE>T48 </SIZE>
> <Text />
> </MovexItem>
> <MovexItem>
> <ID>2</ID>
> <ITDS />
> <ITNO>A00600HH9005154 </ITNO>
> <ORTP />
> <JDCD>001</JDCD>
> <ORQT>1</ORQT>
> <SAPR>0</SAPR>
> <PONR>0</PONR>
> <MODEL6>A00600</MODEL6>
> <MODEL2>HH</MODEL2>
> <SERIES>900</SERIES>
> <TYPE>H</TYPE>
> <COLOR>51</COLOR>
> <SIZE>T54 </SIZE>
> <Text />
> </MovexItem>
> </Items>
> <ORNO />
> <CONO>400</CONO>
> <WHLO />
> <CUNO>H0600001 </CUNO>
> <CUNM />
> <CMPN>05PV</CMPN>
> <ORTP>NOR</ORTP>
> <FACI>A01</FACI>
> <CUOR />
> <ORDT>28/09/2004</ORDT>
> <RLDT>14/01/2005</RLDT>
> <OFNO>ENE</OFNO>
> <ORST>0</ORST>
> <STAT />
> <ADID />
> <ADDR />
> <ID>-1</ID>
> <Text />
> </MovexOrder>'
> SELECT ID, Features
> FROM OpenXML(@.h,'/MovexOrder/Items/MovexItem', 2)
> WITH (ID INT, Features ntext )
> WHERE Features IS NOT NULL
>
>