OpenXML XPath question

I've included the xml and my OpenXML statement below. Using XPath in the
WITH portion of the OpenXML statement I can retrieve most values I need, but
I am trying to retrieve the values 'Last Name of Client' and 'First Name of
Client' from the xml and am not having any luck. I tried using '..' as the
xpath query but that returns text in the Question node and all child nodes.
<Section Value="AA"> Name and Identification Numbers
<Question Value="1.A"> Last Name of Client
<Question_Text>Last</Question_Text>
<Answer_Text>ROSSI1</Answer_Text>
<Answer_Value>ROSSI2</Answer_Value>
</Question>
<Question Value="2.A"> First Name of Client
<Question_Text>First Name</Question_Text>
<Answer_Text>TestA1</Answer_Text>
<Answer_Value>TestA2</Answer_Value>
</Question>
</Section>
OpenXML(@.idoc, '/ns0:GoldCare_Assessment/Section/Question')
WITH ([SectionValue] varchar(50) '../@.Value',
[QuestionValue] varchar(50) '@.Value',
[Question_Text] varchar(50) './Question_Text',
[Answer_Text] varchar(50) './Answer_Text',
[Answer_Value] varchar(50) './Answer_Value')Look at the bottom of this article where the stored procedure is shown:
http://www.eggheadcafe.com/articles...er_bulkload.asp
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/for...st10017013.aspx
http://www.eggheadcafe.com/articles...e_generator.asp
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:e1us7sSGFHA.2756@.TK2MSFTNGP15.phx.gbl...
> I've included the xml and my OpenXML statement below. Using XPath in the
> WITH portion of the OpenXML statement I can retrieve most values I need,
> but
> I am trying to retrieve the values 'Last Name of Client' and 'First Name
> of
> Client' from the xml and am not having any luck. I tried using '..' as
> the
> xpath query but that returns text in the Question node and all child
> nodes.
> <Section Value="AA"> Name and Identification Numbers
> <Question Value="1.A"> Last Name of Client
> <Question_Text>Last</Question_Text>
> <Answer_Text>ROSSI1</Answer_Text>
> <Answer_Value>ROSSI2</Answer_Value>
> </Question>
> <Question Value="2.A"> First Name of Client
> <Question_Text>First Name</Question_Text>
> <Answer_Text>TestA1</Answer_Text>
> <Answer_Value>TestA2</Answer_Value>
> </Question>
> </Section>
> OpenXML(@.idoc, '/ns0:GoldCare_Assessment/Section/Question')
> WITH ([SectionValue] varchar(50) '../@.Value',
> [QuestionValue] varchar(50) '@.Value',
> [Question_Text] varchar(50) './Question_Text',
> [Answer_Text] varchar(50) './Answer_Text',
> [Answer_Value] varchar(50) './Answer_Value')
>
>

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
>
>