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
 General SQL Server Forums
 New to SQL Server Programming
 XML column data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dolphin123
Yak Posting Veteran

USA
78 Posts

Posted - 08/12/2013 :  12:48:57  Show Profile  Reply with Quote
Hi,

I have an XML column which has logs from certain web requests.
I want to extract few information and show them in different columsn.

I can show "CallerFirmId" ID using this:

,[Body].value('(/LogMessage/Context/Object/CallerFirmId)[1]', 'varchar(max)') FirmID


but how do I find the AdvisorID, which shows as this in XML, as there are many Name, value pairs. THANK FOR YOUR HELP IN ADVANCE.

            <NameValue>
              <Name>AdvisorId</Name>
              <Value>P1234</Value>
            </NameValue>



    <Context>
    <Object xmlns="">
      <CallerFirmId>19</CallerFirmId>
      <QueryCount>1</QueryCount>
      <RequestId>r163508246149769293758</RequestId>
      <Token />
      <RC>0</RC>
      <ElapsedTimeMs>218</ElapsedTimeMs>
      <MtTimeMs>234</MtTimeMs>
      <DacTimeMs>233</DacTimeMs>
      <ActiveCallCount>1</ActiveCallCount>
    </Object>
  </Context>
  <RequestStart>2013-07-01T07:29:36.771875Z</RequestStart>
  <RequestEnd>2013-07-01T07:29:36.990625Z</RequestEnd>
  <RequestIp>20.20.20.20</RequestIp>
  <RequestUri>/MYAPP/Api/MultiQuery</RequestUri>
  <RequestData>
    <MultiQueryRequest xmlns="http://schemas.datacontract.org/2004/07/myapp.something">
      <LastClientSvcTimeMs>0</LastClientSvcTimeMs>
      <LastRequestId xmlns:i="http://www.w3.org/2001/XMLSchema-instance" i:nil="true" />
      <Token xmlns:i="http://www.w3.org/2001/XMLSchema-instance" i:nil="true" />
      <Queries>
        <MultiQueryItem>
          <ClientRequestId>1</ClientRequestId>
          <Params>
            <NameValue>
              <Name>Units</Name>
              <Value>214</Value>
            </NameValue>
            <NameValue>
              <Name>Price</Name>
              <Value>396.15</Value>
            </NameValue>
            <NameValue>
              <Name>MarketOrFirm</Name>
              <Value>0</Value>
            </NameValue>
            <NameValue>
              <Name>ScheduledCommission</Name>
              <Value>100</Value>
            </NameValue>
            <NameValue>
              <Name>ProposedCommission</Name>
              <Value>100</Value>
            </NameValue>
            <NameValue>
              <Name>AdvisorId</Name>
              <Value>P1234</Value>
            </NameValue>
            <NameValue>
              <Name>LanguageId</Name>
              <Value>1</Value>
            </NameValue>
            <NameValue>
              <Name>WithLabels</Name>
              <Value>false</Value>
            </NameValue>
          </Params>
          <ProcessName>MYPROCESS</ProcessName>
        </MultiQueryItem>
      </Queries>
    </MultiQueryRequest>
  </RequestData>

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 08/12/2013 :  13:17:02  Show Profile  Reply with Quote
Since your XML is not complete and not well-formed, I am unable to say anything more than general principles.

In general, one would navigate down to a node just above where your data resides in your from clause AND allows you to relate the various pieces of data that you want to pick up. Then get the data in the select clause by navigating to the node from that node.

The following is a simplified example that you can copy and paste to an SSMS query window to run and see what it does:
DECLARE @X XML  = '
<Object xmlns="">
  <CallerFirmId>19</CallerFirmId>
  <QueryCount>1</QueryCount>
  <RequestId>r163508246149769293758</RequestId>
  <Token />
  <RC>0</RC>
  <SomeOtherData>
    <Data>1</Data>
    <Data>2</Data>
    <Data>3</Data>
  </SomeOtherData>
  <ElapsedTimeMs>218</ElapsedTimeMs>
  <MtTimeMs>234</MtTimeMs>
  <DacTimeMs>233</DacTimeMs>
  <ActiveCallCount>1</ActiveCallCount>
</Object>

'
SELECT
	c.value('./RequestId[1]','varchar(32)') AS RequestId,
	c2.value('.','Int') AS OtherData
FROM
	@X.nodes('/Object') T(c)
	CROSS APPLY c.nodes('SomeOtherData/Data')T2(c2);
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/12/2013 :  13:33:35  Show Profile  Reply with Quote
Or something like this:


DECLARE @x AS XML;
SET @x=N'
    <Context>
    <Object xmlns="">
      <CallerFirmId>19</CallerFirmId>
      <QueryCount>1</QueryCount>
      <RequestId>r163508246149769293758</RequestId>
      <Token />
      <RC>0</RC>
      <ElapsedTimeMs>218</ElapsedTimeMs>
      <MtTimeMs>234</MtTimeMs>
      <DacTimeMs>233</DacTimeMs>
      <ActiveCallCount>1</ActiveCallCount>
    </Object>
  </Context>
  <RequestStart>2013-07-01T07:29:36.771875Z</RequestStart>
  <RequestEnd>2013-07-01T07:29:36.990625Z</RequestEnd>
  <RequestIp>20.20.20.20</RequestIp>
  <RequestUri>/MYAPP/Api/MultiQuery</RequestUri>
  <RequestData>
    <MultiQueryRequest xmlns="http://schemas.datacontract.org/2004/07/myapp.something">
      <LastClientSvcTimeMs>0</LastClientSvcTimeMs>
      <LastRequestId xmlns:i="http://www.w3.org/2001/XMLSchema-instance" i:nil="true" />
      <Token xmlns:i="http://www.w3.org/2001/XMLSchema-instance" i:nil="true" />
      <Queries>
        <MultiQueryItem>
          <ClientRequestId>1</ClientRequestId>
          <Params>
            <NameValue>
              <Name>Units</Name>
              <Value>214</Value>
            </NameValue>
            <NameValue>
              <Name>Price</Name>
              <Value>396.15</Value>
            </NameValue>
            <NameValue>
              <Name>MarketOrFirm</Name>
              <Value>0</Value>
            </NameValue>
            <NameValue>
              <Name>ScheduledCommission</Name>
              <Value>100</Value>
            </NameValue>
            <NameValue>
              <Name>ProposedCommission</Name>
              <Value>100</Value>
            </NameValue>
            <NameValue>
              <Name>AdvisorId</Name>
              <Value>P1234</Value>
            </NameValue>
            <NameValue>
              <Name>LanguageId</Name>
              <Value>1</Value>
            </NameValue>
            <NameValue>
              <Name>WithLabels</Name>
              <Value>false</Value>
            </NameValue>
          </Params>
          <ProcessName>MYPROCESS</ProcessName>
        </MultiQueryItem>
      </Queries>
    </MultiQueryRequest>
  </RequestData>';

;WITH CTE AS
(SELECT	n.value('local-name(.)', 'VARCHAR(MAX)') AS LocalName,
		n.value('.', 'VARCHAR(MAX)') AS Data,
		ROW_NUMBER() OVER (ORDER BY d.n) - 1 AS rn
	FROM @x.nodes('/RequestData/*/*/*/*/*/*') AS d(n))
SELECT Data From CTE WHERE rn = (SELECT rn from CTE WHERE Data = 'AdvisorId') +1;

Go to Top of Page

dolphin123
Yak Posting Veteran

USA
78 Posts

Posted - 08/12/2013 :  13:33:57  Show Profile  Reply with Quote
Thanks a lot James for your reply. I will check your example and see if I can get values from name value pairs inside XML nodes.
Go to Top of Page

dolphin123
Yak Posting Veteran

USA
78 Posts

Posted - 08/12/2013 :  13:40:26  Show Profile  Reply with Quote
Thanks a lot MuMu88. I will give that a shot as well. Much appreciated.
Go to Top of Page

dolphin123
Yak Posting Veteran

USA
78 Posts

Posted - 08/12/2013 :  15:13:56  Show Profile  Reply with Quote
Hi Mumu88,

Thanks. I used your code and I was able to get these for every request. (below code).

As you can, I need the data to be on a column so the best I can do is use the code below and get column as as this:


Units	Price	MarketOrFirm	ScheduledCommission	ProposedCommission	AdvisorId	LanguageId	WithLabels
Units214	Price396.15	MarketOrFirm0	ScheduledCommission100	ProposedCommission100	AdvisorIdP1234	LanguageId1	WithLabelsfalse





select 
@x.value('(/RequestData/*/*/*/*/*)[1]','varchar(max)') Units,
@x.value('(/RequestData/*/*/*/*/*)[2]','varchar(max)') Price,
@x.value('(/RequestData/*/*/*/*/*)[3]','varchar(max)') MarketOrFirm,
@x.value('(/RequestData/*/*/*/*/*)[4]','varchar(max)') ScheduledCommission,
@x.value('(/RequestData/*/*/*/*/*)[5]','varchar(max)') ProposedCommission,
@x.value('(/RequestData/*/*/*/*/*)[6]','varchar(max)') AdvisorId,
@x.value('(/RequestData/*/*/*/*/*)[7]','varchar(max)') LanguageId,
@x.value('(/RequestData/*/*/*/*/*)','varchar(max)') WithLabels



Which is not bad. But Is there way to just to show the Value? I know I can use the code where you are using a CTE to get the value, but I need to display all the values for each request to display in a row. So I can't use CTE on each request. Is there a simpler way?

Thanks again.

D
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/12/2013 :  21:28:43  Show Profile  Reply with Quote
Try this:


;WITH CTE AS
(SELECT	n.value('local-name(.)', 'VARCHAR(MAX)') AS LocalName,
		n.value('.', 'VARCHAR(MAX)') AS Data,
	 ROW_NUMBER() OVER (ORDER BY d.n)  AS rn1
	FROM @x.nodes('/RequestData/*/*/*/*/*/*') AS d(n)),
CTE1 AS
(SELECT ROW_NUMBER() OVER (PARTITION BY C.LocalName ORDER BY rn1) AS rn, LocalName, Data FROM CTE C),
CTE2 AS 
(SELECT rn, [Name], [Value] FROM 
(SELECT rn, LocalName, Data FROM  CTE1) C
PIVOT (MAX(Data) FOR LocalName IN ([Name],[Value]))p)
SELECT * FROM 
(SELECT [Name],[Value] FROM CTE2) C1
PIVOT (MAX(Value) FOR Name IN ([Units],[Price], [MarketOrFirm], [ScheduledCommission], [ProposedCommission], [AdvisorId], [LanguageId], [WithLabels]))p

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