Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Extracting element from XML not working
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Corobori
Posting Yak Master

Chile
105 Posts

Posted - 06/15/2013 :  12:47:42  Show Profile  Visit Corobori's Homepage  Reply with Quote
I want to extract elements from a Ntext column convert to XML. I don't get anything


DECLARE @T TABLE (ErrorId int, AllXML NTEXT)

INSERT INTO @T 
   SELECT 1, ' 
<error host="DEDI546" type="System.Web.HttpException" message="The file '' /www.mejoresdatos.cl/topics.aspx''  does not exist." source="System.Web" detail="System.Web.HttpException: The file '' /www.mejoresdatos.cl/topics.aspx''  does not exist.#xD;#xA;   at System.Web.UI.Util.CheckVirtualFileExists(VirtualPath virtualPath)#xD;#xA;   at System.Web.Compilation.BuildManager.GetVPathBuildResultInternal(VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile)#xD;#xA;   at System.Web.Compilation.BuildManager.GetVPathBuildResultWithNoAssert(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile)#xD;#xA;   at System.Web.Compilation.BuildManager.GetVirtualPathObjectFactory(VirtualPath virtualPath, HttpContext context, Boolean allowCrossApp, Boolean noAssert)#xD;#xA;   at System.Web.Compilation.BuildManager.CreateInstanceFromVirtualPath(VirtualPath virtualPath, Type requiredBaseType, HttpContext context, Boolean allowCrossApp, Boolean noAssert)#xD;#xA;   at System.Web.UI.PageHandlerFactory.GetHandlerHelper(HttpContext context, String requestType, VirtualPath virtualPath, String physicalPath)#xD;#xA;   at System.Web.UI.PageHandlerFactory.System.Web.IHttpHandlerFactory2.GetHandler(HttpContext context, String requestType, VirtualPath virtualPath, String physicalPath)#xD;#xA;   at System.Web.HttpApplication.MapHttpHandler(HttpContext context, String requestType, VirtualPath path, String pathTranslated, Boolean useAppConfig)#xD;#xA;   at System.Web.HttpApplication.MapHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()#xD;#xA;   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)" time="2011-04-23T21:19:21.9591505Z" statusCode="404">
  <serverVariables>
    <item name="ALL_HTTP">
      <value string="HTTP_CONNECTION:Keep-Alive#xD;#xA;HTTP_VIA:1.0 BRJOPXY01#xD;#xA;HTTP_ACCEPT:image/gif, image/jpeg, image/pjpeg, application/x-ms-application, application/vnd.ms-xpsdocument, application/xaml+xml, application/x-ms-xbap, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, application/x-shockwave-flash, */*#xD;#xA;HTTP_COOKIE:ASP.NET_SessionId=r2cqf5mz5wyq3rbdbv2vmc45#xD;#xA;HTTP_HOST:www.mejoresdatos.cl#xD;#xA;HTTP_REFERER:www.mejoresdatos.cl/forum1-concepcion.aspx#xD;#xA;HTTP_USER_AGENT:Mozilla/4.0" target="_blank">http://www.mejoresdatos.cl//www.mejoresdatos.cl/forum1-concepcion.aspx#xD;#xA;HTTP_USER_AGENT:Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)#xD;#xA;" />
    </item>
    <item name="ALL_RAW">
      <value string="Connection: Keep-Alive#xD;#xA;Via: 1.0 BRJOPXY01#xD;#xA;Accept: image/gif, image/jpeg, image/pjpeg, application/x-ms-application, application/vnd.ms-xpsdocument, application/xaml+xml, application/x-ms-xbap, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, application/x-shockwave-flash, */*#xD;#xA;Cookie: ASP.NET_SessionId=r2cqf5mz5wyq3rbdbv2vmc45#xD;#xA;Host: www.mejoresdatos.cl#xD;#xA;Referer: www.mejoresdatos.cl/forum1-concepcion.aspx#xD;#xA;User-Agent:" target="_blank">http://www.mejoresdatos.cl//www.mejoresdatos.cl/forum1-concepcion.aspx#xD;#xA;User-Agent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)#xD;#xA;" />
    </item>
    <item name="APPL_MD_PATH">
      <value string="/LM/w3svc/2005001/ROOT" />
    </item>
    <item name="APPL_PHYSICAL_PATH">
      <value string="e:\hosting\member\corobori2006\" />
    </item>
    <item name="AUTH_TYPE">
      <value string="" />
    </item>
    <item name="AUTH_USER">
      <value string="" />
    </item>
    <item name="AUTH_PASSWORD">
      <value string="" />
    </item>
    <item name="LOGON_USER">
      <value string="" />
    </item>
    <item name="REMOTE_USER">
      <value string="" />
    </item>
    <item name="CERT_COOKIE">
      <value string="" />
    </item>
    <item name="CERT_FLAGS">
      <value string="" />
    </item>
    <item name="CERT_ISSUER">
      <value string="" />
    </item>
    <item name="CERT_KEYSIZE">
      <value string="" />
    </item>
    <item name="CERT_SECRETKEYSIZE">
      <value string="" />
    </item>
    <item name="CERT_SERIALNUMBER">
      <value string="" />
    </item>
    <item name="CERT_SERVER_ISSUER">
      <value string="" />
    </item>
    <item name="CERT_SERVER_SUBJECT">
      <value string="" />
    </item>
    <item name="CERT_SUBJECT">
      <value string="" />
    </item>
    <item name="CONTENT_LENGTH">
      <value string="0" />
    </item>
    <item name="CONTENT_TYPE">
      <value string="" />
    </item>
    <item name="GATEWAY_INTERFACE">
      <value string="CGI/1.1" />
    </item>
    <item name="HTTPS">
      <value string="off" />
    </item>
    <item name="HTTPS_KEYSIZE">
      <value string="" />
    </item>
    <item name="HTTPS_SECRETKEYSIZE">
      <value string="" />
    </item>
    <item name="HTTPS_SERVER_ISSUER">
      <value string="" />
    </item>
    <item name="HTTPS_SERVER_SUBJECT">
      <value string="" />
    </item>
    <item name="INSTANCE_ID">
      <value string="2005001" />
    </item>
    <item name="INSTANCE_META_PATH">
      <value string="/LM/W3SVC/2005001" />
    </item>
    <item name="LOCAL_ADDR">
      <value string="208.118.55.33" />
    </item>
    <item name="PATH_INFO">
      <value string="/www.mejoresdatos.cl/topics.aspx" />
    </item>
    <item name="PATH_TRANSLATED">
      <value string="e:\hosting\member\corobori2006\www.mejoresdatos.cl\topics.aspx" />
    </item>
    <item name="QUERY_STRING">
      <value string="ForumID=1&" />
    </item>
    <item name="REMOTE_ADDR">
      <value string="200.214.72.248" />
    </item>
    <item name="REMOTE_HOST">
      <value string="200.214.72.248" />
    </item>
    <item name="REMOTE_PORT">
      <value string="19801" />
    </item>
    <item name="REQUEST_METHOD">
      <value string="GET" />
    </item>
    <item name="SCRIPT_NAME">
      <value string="/www.mejoresdatos.cl/topics.aspx" />
    </item>
    <item name="SERVER_NAME">
      <value string="www.mejoresdatos.cl" />
    </item>
    <item name="SERVER_PORT">
      <value string="80" />
    </item>
    <item name="SERVER_PORT_SECURE">
      <value string="0" />
    </item>
    <item name="SERVER_PROTOCOL">
      <value string="HTTP/1.1" />
    </item>
    <item name="SERVER_SOFTWARE">
      <value string="Microsoft-IIS/6.0" />
    </item>
    <item name="URL">
      <value string="/www.mejoresdatos.cl/topics.aspx" />
    </item>
    <item name="HTTP_CONNECTION">
      <value string="Keep-Alive" />
    </item>
    <item name="HTTP_VIA">
      <value string="1.0 BRJOPXY01" />
    </item>
    <item name="HTTP_ACCEPT">
      <value string="image/gif, image/jpeg, image/pjpeg, application/x-ms-application, application/vnd.ms-xpsdocument, application/xaml+xml, application/x-ms-xbap, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, application/x-shockwave-flash, */*" />
    </item>
    <item name="HTTP_COOKIE">
      <value string="ASP.NET_SessionId=r2cqf5mz5wyq3rbdbv2vmc45" />
    </item>
    <item name="HTTP_HOST">
      <value string="www.mejoresdatos.cl" />
    </item>
    <item name="HTTP_REFERER">
      <value string="www.mejoresdatos.cl/forum1-concepcion.aspx" target="_blank">http://www.mejoresdatos.cl//www.mejoresdatos.cl/forum1-concepcion.aspx" />
    </item>
    <item name="HTTP_USER_AGENT">
      <value string="Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)" />
    </item>
  </serverVariables>
  <queryString>
    <item name="ForumID">
      <value string="1" />
    </item>
    <item name="">
      <value string="" />
    </item>
  </queryString>
  <cookies>
    <item name="ASP.NET_SessionId">
      <value string="r2cqf5mz5wyq3rbdbv2vmc45" />
    </item>
  </cookies>
</error>'

;WITH t_xml (ErrorId, AllXML) AS
(
  SELECT ErrorId, CONVERT(XML, CONVERT(NVARCHAR(MAX), AllXml))
   FROM @t 
)
SELECT ErrorId, 
      T.C.value('./text()[1]', 'nvarchar(50)') data
   FROM t_xml
      CROSS APPLY t_xml.AllXML.nodes('//error/serverVariables/SERVER_NAME') T(C)
      


jean-luc
www.corobori.com

Edited by - Corobori on 06/15/2013 12:48:34

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 06/15/2013 :  15:20:51  Show Profile  Reply with Quote
the posted XML doesnt parse for me


Msg 9421, Level 16, State 1, Line 173
XML parsing: line 2, character 1731, illegal name character


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Corobori
Posting Yak Master

Chile
105 Posts

Posted - 06/15/2013 :  20:45:28  Show Profile  Visit Corobori's Homepage  Reply with Quote
quote:
Originally posted by visakh16

the posted XML doesnt parse for me



It should be all right now


DECLARE @T TABLE (ErrorId int, AllXML NTEXT)

INSERT INTO @T 
   SELECT 1, ' 
<error host="DEDI546" >
  <serverVariables>
 
    <item name="APPL_MD_PATH">
      <value string="/LM/w3svc/2005001/ROOT" />
    </item>
    <item name="APPL_PHYSICAL_PATH">
      <value string="e:\hosting\member\corobori2006\" />
    </item>
    <item name="AUTH_TYPE">
      <value string="" />
    </item>
    <item name="AUTH_USER">
      <value string="" />
    </item>
    <item name="AUTH_PASSWORD">
      <value string="" />
    </item>
    <item name="LOGON_USER">
      <value string="" />
    </item>
    <item name="REMOTE_USER">
      <value string="" />
    </item>
    <item name="CERT_COOKIE">
      <value string="" />
    </item>
    <item name="CERT_FLAGS">
      <value string="" />
    </item>
    <item name="CERT_ISSUER">
      <value string="" />
    </item>
    <item name="CERT_KEYSIZE">
      <value string="" />
    </item>
    <item name="CERT_SECRETKEYSIZE">
      <value string="" />
    </item>
    <item name="CERT_SERIALNUMBER">
      <value string="" />
    </item>
    <item name="CERT_SERVER_ISSUER">
      <value string="" />
    </item>
    <item name="CERT_SERVER_SUBJECT">
      <value string="" />
    </item>
    <item name="CERT_SUBJECT">
      <value string="" />
    </item>
    <item name="CONTENT_LENGTH">
      <value string="0" />
    </item>
    <item name="CONTENT_TYPE">
      <value string="" />
    </item>
    <item name="GATEWAY_INTERFACE">
      <value string="CGI/1.1" />
    </item>
    <item name="HTTPS">
      <value string="off" />
    </item>
    <item name="HTTPS_KEYSIZE">
      <value string="" />
    </item>
    <item name="HTTPS_SECRETKEYSIZE">
      <value string="" />
    </item>
    <item name="HTTPS_SERVER_ISSUER">
      <value string="" />
    </item>
    <item name="HTTPS_SERVER_SUBJECT">
      <value string="" />
    </item>
    <item name="INSTANCE_ID">
      <value string="2005001" />
    </item>
    <item name="INSTANCE_META_PATH">
      <value string="/LM/W3SVC/2005001" />
    </item>
    <item name="LOCAL_ADDR">
      <value string="208.118.55.33" />
    </item>
    <item name="PATH_INFO">
      <value string="/www.mejoresdatos.cl/topics.aspx" />
    </item>
    <item name="PATH_TRANSLATED">
      <value string="e:\hosting\member\corobori2006\www.mejoresdatos.cl\topics.aspx" />
    </item>

    <item name="REMOTE_ADDR">
      <value string="200.214.72.248" />
    </item>
    <item name="REMOTE_HOST">
      <value string="200.214.72.248" />
    </item>
    <item name="REMOTE_PORT">
      <value string="19801" />
    </item>
    <item name="REQUEST_METHOD">
      <value string="GET" />
    </item>
    <item name="SCRIPT_NAME">
      <value string="/www.mejoresdatos.cl/topics.aspx" />
    </item>
    <item name="SERVER_NAME">
      <value string="www.mejoresdatos.cl" />
    </item>
    <item name="SERVER_PORT">
      <value string="80" />
    </item>
    <item name="SERVER_PORT_SECURE">
      <value string="0" />
    </item>
    <item name="SERVER_PROTOCOL">
      <value string="HTTP/1.1" />
    </item>
    <item name="SERVER_SOFTWARE">
      <value string="Microsoft-IIS/6.0" />
    </item>
    <item name="URL">
      <value string="/www.mejoresdatos.cl/topics.aspx" />
    </item>
    <item name="HTTP_CONNECTION">
      <value string="Keep-Alive" />
    </item>
    <item name="HTTP_VIA">
      <value string="1.0 BRJOPXY01" />
    </item>
    <item name="HTTP_ACCEPT">
      <value string="image/gif, image/jpeg, image/pjpeg, application/x-ms-application, application/vnd.ms-xpsdocument, application/xaml+xml, application/x-ms-xbap, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, application/x-shockwave-flash, */*" />
    </item>
  
    <item name="HTTP_HOST">
      <value string="www.mejoresdatos.cl" />
    </item>
  
    <item name="HTTP_USER_AGENT">
      <value string="Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)" />
    </item>
  </serverVariables>
  <queryString>
    <item name="ForumID">
      <value string="1" />
    </item>
    <item name="">
      <value string="" />
    </item>
  </queryString>
  <cookies>
    <item name="ASP.NET_SessionId">
      <value string="r2cqf5mz5wyq3rbdbv2vmc45" />
    </item>
  </cookies>
</error>'

;WITH t_xml (ErrorId, AllXML) AS
(
  SELECT ErrorId, CONVERT(XML, CONVERT(NVARCHAR(MAX), AllXml))
   FROM @t 
)
SELECT ErrorId, 
      T.C.value('./text()[1]', 'nvarchar(50)') data
   FROM t_xml
      CROSS APPLY t_xml.AllXML.nodes('//error/serverVariables/PATH_INFO') T(C)


jean-luc
www.corobori.com
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 06/15/2013 :  22:06:58  Show Profile  Reply with Quote
Try this:


;WITH t_xml (ErrorId, AllXML) AS
(
  SELECT ErrorId, CONVERT(XML, CONVERT(NVARCHAR(MAX), AllXml))
   FROM @t 
)
SELECT ErrorId, 
      T.C.value('./@name[1]', 'nvarchar(50)') data
   FROM t_xml
      CROSS APPLY t_xml.AllXML.nodes('//error/serverVariables/item') T(C)

-- OR THIS:


;WITH t_xml (ErrorId, AllXML) AS
(
  SELECT ErrorId, CONVERT(XML, CONVERT(NVARCHAR(MAX), AllXml))
   FROM @t 
)
SELECT ErrorId, 
      T.C.value('./@string[1]', 'nvarchar(50)') data
   FROM t_xml
      CROSS APPLY t_xml.AllXML.nodes('/error/serverVariables/item/value') T(C)


Edited by - MuMu88 on 06/15/2013 22:16:23
Go to Top of Page

Corobori
Posting Yak Master

Chile
105 Posts

Posted - 06/15/2013 :  23:18:24  Show Profile  Visit Corobori's Homepage  Reply with Quote
quote:
Originally posted by MuMu88

Try this:



In the case of this node

    <item name="APPL_MD_PATH">
      <value string="/LM/w3svc/2005001/ROOT" />
    </item>


The 1st one gives "APPL_MD_PATH"
The 2nd one gives "/LM/w3svc/2005001/ROOT"

What I would like is to have something like
"APPL_MD_PATH","/LM/w3svc/2005001/ROOT"

I tried fiddling around the SQL shown above but I don't grasp the node concept.



jean-luc
www.corobori.com
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 06/16/2013 :  00:05:16  Show Profile  Reply with Quote
Try this:



;WITH t_xml (ErrorId, AllXML) AS
(
  SELECT ErrorId, CONVERT(XML, CONVERT(NVARCHAR(MAX), AllXml))
   FROM @t 
)
SELECT ErrorId, 
	  T.C.value('./@name[1]', 'nvarchar(50)') Value,
	  T.C.value('(./value/@string)[1]', 'nvarchar(50)') data
   FROM t_xml
      CROSS APPLY t_xml.AllXML.nodes('/error/serverVariables/item') T(C)

Edited by - MuMu88 on 06/16/2013 00:07:41
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000