Prev: Organizing Solutions and Projects
Next: Flat File Import suggestions?
From: gv on 28 Jul 2010 08:41
Hello,

Using SQL 2008

I'm taking xml string from a VARCHAR(max) datatype column and inserting into
XML datatype column then
want to query it.

Doing the above like so:

DECLARE @XmlSourceTable TABLE
(RecordId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
XmlData XML NOT NULL )
INSERT INTO @XmlSourceTable
(XmlData)
SELECT TOP 1 CONVERT(XML,REPLACE(REPLACE(CSR, '&lt;', '<'), '&gt;', '>'),2)
FROM CDATA

The xml looks like this after inserting

here is just a small part:

<SoapGetCSRReturnResponse xmlns="http://tempuri.org/XfaceXML/Ordering">
<SoapGetCSRReturnResult>
<MessageHeader>
<RBOC_NO>ATT </RBOC_NO>
<ORD_NO>A567565699</ORD_NO>
<CSRD>
<Header>
<RBOC_NO>ATT </RBOC_NO>
<RESELLER_NO>ACP</RESELLER_NO>
<ORD_NO>A567565699</ORD_NO>
<DATE_RECEIVED>2010-06-17T08:17:43.527</DATE_RECEIVED>
...........
...........
..........


Simple I want query the data, example pull out :
RBOC_NO,ORD_NO, etc.. from the CRSD Element?

thanks,
gv


From: gv on 28 Jul 2010 11:03
I'm doing this and seems to be working.

;WITH XMLNAMESPACES( 'http://tempuri.org/XfaceXML/Ordering' AS t )
SELECT
x.y.value('t:RBOC_NO[1]', 'VARCHAR(30)') AS [RBOC Number]
FROM @XmlSourceTable CSR
CROSS APPLY
CSR.XmlData.nodes('/t:SoapGetCSRReturnResponse/t:SoapGetCSRReturnResult/t:MessageHeader')
x(y)

is this the correct way? and do I just add another CROSS APPLY to get sub
elements with the extended path?

thanks
gv

"gv" <viator.gerry(a)gmail.com> wrote in message
news:%23nHf1IlLLHA.4780(a)TK2MSFTNGP02.phx.gbl...
> Hello,
>
> Using SQL 2008
>
> I'm taking xml string from a VARCHAR(max) datatype column and inserting
> into XML datatype column then
> want to query it.
>
> Doing the above like so:
>
> DECLARE @XmlSourceTable TABLE
> (RecordId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
> XmlData XML NOT NULL )
> INSERT INTO @XmlSourceTable
> (XmlData)
> SELECT TOP 1 CONVERT(XML,REPLACE(REPLACE(CSR, '&lt;', '<'), '&gt;',
> '>'),2)
> FROM CDATA
>
> The xml looks like this after inserting
>
> here is just a small part:
>
> <SoapGetCSRReturnResponse xmlns="http://tempuri.org/XfaceXML/Ordering">
> <SoapGetCSRReturnResult>
> <MessageHeader>
> <RBOC_NO>ATT </RBOC_NO>
> <ORD_NO>A567565699</ORD_NO>
> <CSRD>
> <Header>
> <RBOC_NO>ATT </RBOC_NO>
> <RESELLER_NO>ACP</RESELLER_NO>
> <ORD_NO>A567565699</ORD_NO>
> <DATE_RECEIVED>2010-06-17T08:17:43.527</DATE_RECEIVED>
> ..........
> ..........
> .........
>
>
> Simple I want query the data, example pull out :
> RBOC_NO,ORD_NO, etc.. from the CRSD Element?
>
> thanks,
> gv
>
>


From: Erland Sommarskog on 28 Jul 2010 18:26
gv (viator.gerry(a)gmail.com) writes:
> I'm doing this and seems to be working.
>
> ;WITH XMLNAMESPACES( 'http://tempuri.org/XfaceXML/Ordering' AS t )
> SELECT
> x.y.value('t:RBOC_NO[1]', 'VARCHAR(30)') AS [RBOC Number]
> FROM @XmlSourceTable CSR
> CROSS APPLY
> CSR.XmlData.nodes('/t:SoapGetCSRReturnResponse/t:SoapGetCSRReturnResult/t:MessageHeader')
> x(y)
>
> is this the correct way? and do I just add another CROSS APPLY to get sub
> elements with the extended path?

I think you are on the right track.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Organizing Solutions and Projects
Next: Flat File Import suggestions?