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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Extracting element from XML not working

Author  Topic 

Corobori
Posting Yak Master

105 Posts
Posted - 2013-06-15 : 12:47:42
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: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: 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="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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-15 : 15:20:51
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

105 Posts
Posted - 2013-06-15 : 20:45:28
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 - 2013-06-15 : 22:06:58
Try this:
[CODE]

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

[/CODE]
Go to Top of Page

Corobori
Posting Yak Master

105 Posts
Posted - 2013-06-15 : 23:18:24
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 - 2013-06-16 : 00:05:16
Try this:
[CODE]


;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)
[/CODE]
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site