SQL Server Forums
Profile | Register | 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
 New Topic  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
52249 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

547 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

547 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  
 New 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.09 seconds. Powered By: Snitz Forums 2000