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
>
>
Showing posts with label content. Show all posts
Showing posts with label content. Show all posts
Saturday, February 25, 2012
Monday, February 20, 2012
OpenXML query
I am trying to create a stored procedure which opens a file in say location
'C:\xml'
and then pass the content of this opened xml file once opened to OPenXml
does anyone know how to do this.
Thanks in advance.You would have to write an extended stored proc or use the sp_OA stored
proc. In SQL Server 2005, there will be an easier way to read from a file...
Best regards
Michael
"gee57" <gee57@.discussions.microsoft.com> wrote in message
news:E0CBF1EB-2D62-45D5-8C82-A20C3BAC1876@.microsoft.com...
>I am trying to create a stored procedure which opens a file in say location
> 'C:\xml'
>
> and then pass the content of this opened xml file once opened to OPenXml
> does anyone know how to do this.
> Thanks in advance.
'C:\xml'
and then pass the content of this opened xml file once opened to OPenXml
does anyone know how to do this.
Thanks in advance.You would have to write an extended stored proc or use the sp_OA stored
proc. In SQL Server 2005, there will be an easier way to read from a file...
Best regards
Michael
"gee57" <gee57@.discussions.microsoft.com> wrote in message
news:E0CBF1EB-2D62-45D5-8C82-A20C3BAC1876@.microsoft.com...
>I am trying to create a stored procedure which opens a file in say location
> 'C:\xml'
>
> and then pass the content of this opened xml file once opened to OPenXml
> does anyone know how to do this.
> Thanks in advance.
OpenXML query
I am trying to create a stored procedure which opens a file in say location
'C:\xml'
and then pass the content of this opened xml file once opened to OPenXml
does anyone know how to do this.
Thanks in advance.
You would have to write an extended stored proc or use the sp_OA stored
proc. In SQL Server 2005, there will be an easier way to read from a file...
Best regards
Michael
"gee57" <gee57@.discussions.microsoft.com> wrote in message
news:E0CBF1EB-2D62-45D5-8C82-A20C3BAC1876@.microsoft.com...
>I am trying to create a stored procedure which opens a file in say location
> 'C:\xml'
>
> and then pass the content of this opened xml file once opened to OPenXml
> does anyone know how to do this.
> Thanks in advance.
'C:\xml'
and then pass the content of this opened xml file once opened to OPenXml
does anyone know how to do this.
Thanks in advance.
You would have to write an extended stored proc or use the sp_OA stored
proc. In SQL Server 2005, there will be an easier way to read from a file...
Best regards
Michael
"gee57" <gee57@.discussions.microsoft.com> wrote in message
news:E0CBF1EB-2D62-45D5-8C82-A20C3BAC1876@.microsoft.com...
>I am trying to create a stored procedure which opens a file in say location
> 'C:\xml'
>
> and then pass the content of this opened xml file once opened to OPenXml
> does anyone know how to do this.
> Thanks in advance.
Subscribe to:
Posts (Atom)